CActiveRecord DB relationship with itself

Yii 1.1 General Discussion
1

So in my DB, I have a table that references itself.

Meaning the table has a column defined as "parent_id" and that column references rows within the same table.

What kind of Foreign key setup do I need on the DB end for Yii to be happy? I currently have no foreign keys setup for this column and I get errors like:


The relation "parent" in active record class "Page" is specified with an invalid foreign key. The format of the foreign key must be "joinTable(fk1,fk2,...)".

with any type of flag I pass it in the relations function:




    public function relations() {

        return array(

            'createdBy' => array(self::BELONGS_TO, 'User', 'created_by'),

            'parent' => array(self::MANY_MANY, 'Page', 'parent_id'),

            'pageContents' => array(self::HAS_MANY, 'PageContent', 'page_id'),

            'pageOrders' => array(self::HAS_MANY, 'PageOrder', 'page_id'),

        );

    }

where ‘parent’ is the parent object/row within the table.

2

To:




    public function relations() {

        return array(

            'createdBy' => array(self::BELONGS_TO, 'User', 'created_by'),

            'parent' => array(self::BELONGS_TO, 'Page', 'parent_id'), //the column in 'parent' containing 'this' id

            'children' => array(self::HAS_MANY, 'Page', 'parent_id'), //the column in 'this' containing the child id

            'pageContents' => array(self::HAS_MANY, 'PageContent', 'page_id'),

            'pageOrders' => array(self::HAS_MANY, 'PageOrder', 'page_id'),

        );

    }

I’ve also added a child relation so you can see how that would work.

By the way if you have more than one parent, this wont work.

Hope I’ve helped you.

3

Fantastic. Thank you Luke!