In Yii2, how do I join a table to itself?

Question

I have a page table. Pages can have parents, which are also pages. I want to select all pages with parent_id = NULL and their 'children'. But when I try this

public function getPages()
{
    return $this->hasMany(Page::className(), ['parent_id' => 'id']);
}

I get a get a 1066 Not unique table/alias: 'page' error ... I know how to fix this in Yii1, but I can't figure out to fix it in Yii2.

Answer 1

I have done this for my grades module in which I have parent-child relationship for grades.

Follow this steps:

1. In your model file write this code for self join. We need to alias the table name. Here 'parent' is an alias name.

   public function getParent() {
       return $this->hasOne(self::classname(), ['id' => 'parent_id'])->
                   from(self::tableName() . ' AS parent');
   }
   

2. In your view file write this code

   [
   
     'label' => 'Parent Grade',
   
     'value' => ($model->parent_id != '' || $model->parent_id != 0) ? $model->parent->name : 'Root Grade',
   
   ],
   

Here in my code if the parent_id field is not null/zero then it will display the parent grade name otherwise "Root Grade".

Answer 2

You need to alias the table name.

 $this->hasMany(Category::className(), ['parent_id' => 'id'])->from(['cat' => 'category']);

See discussion here.