We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

relationship between two tables with multiple fields

CREATE TABLE question ( id int(10) unsigned NOT NULL AUTO_INCREMENT, title varchar(150) NOT NULL DEFAULT '', body text NOT NULL, PRIMARY KEY (id), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE answer ( id int(10) NOT NULL, body text NOT NULL, questionId int(10) NOT NULL DEFAULT '0', PRIMARY KEY (id), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE comment ( id int(10) NOT NULL, body varchar(255) NOT NULL, itemType tinyint(3) unsigned NOT NULL DEFAULT '0', // 1 => 'question', 2=> 'answer' itemId int(10) unsigned NOT NULL DEFAULT '0', // questionId or answerId PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

as related comments determined by itemType and itemId, how to define the relationship between question-comment or answer-comment?



2.1k

Currently there is no way of doing that in phalcon. open a nfr. but here's a workaround

function afterFetch()
{
    if($this->type)
        $this->belongsTo("itemid", "question","id");
    else
        $this->belongsTo("itemid", "answer", "id");
}
function afterFetch()
{
    $this->comments = Comments::find(["conditions" => "id = ? & type = 1", "bind" => $this->id]);
}

function afterFetch()
{
    $this->comments = Comments::find(["conditions" => "id = ? & type = 0", "bind" => $this->id]);
}


22.7k
Accepted
answer

do what 7thcubic states or make a question_comments and a answer_comments table and use the ORM has many for each to manage within normal parameters.



69

I think it's not gonna work