yii crud generation for self related table

Hello,

I have a yii2 application with a table Mappingtable that is selfrelated : it contains :

[list=1]

[*]the field crudmaster_id that refers to the id of the same table

[*]the field crudsort_id that refers to the id of Mappingfield

[/list]

The table Mappingfield contains a field mappingtable_id related to the id of Mappingtable

I’m generating tables and foreign keys by migration :

    $this->createTable('{{%mappingtable}}', array(

        'id' => Schema::TYPE_PK,

        'name' => Schema::TYPE_STRING . '(50) NOT NULL',

        'crudsort_id' => 'int(11) DEFAULT NULL',

        'crudmaster_id' => 'int(11) DEFAULT NULL',

        'crudnumbers' => 'boolean',

        'createtime' => 'datetime DEFAULT NULL',

        'createuser_id' => 'int(11) DEFAULT NULL',

        'updatetime' => 'datetime DEFAULT NULL',

        'updateuser_id' => 'int(11) DEFAULT NULL',

    ), $tableOptions);

    $this->insert('{{%tablecolumngroup}}',array('table'=>'Mappingfield','type' => 'form','groups' =>'0,1,2,3,0,0,0,0'));

    $this->insert('{{%tablecolumngroup}}',array('table'=>'Mappingfield','type' => 'index','groups'=>'1,2,3,4,0,0,0,0'));

    $this->insert('{{%tablecolumngroup}}',array('table'=>'Mappingfield','type' => 'view','groups' =>'2,1,1,1,2,2,2,2','descriptions' => 'section.general,section.system',));

    $this->createTable('{{%mappingfield}}', array(

        'id' => Schema::TYPE_PK,

        'mappingtable_id' => 'int(11) NOT NULL',

        'name' => Schema::TYPE_STRING . '(50) NOT NULL',

        'createtime' => 'datetime DEFAULT NULL',

        'createuser_id' => 'int(11) DEFAULT NULL',

        'updatetime' => 'datetime DEFAULT NULL',

        'updateuser_id' => 'int(11) DEFAULT NULL',

    ), $tableOptions);


    $this->addForeignKey("fk_mapf_tabl", "{{%mappingfield}}", "mappingtable_id", "{{%mappingtable}}", "id", "NO ACTION", "NO ACTION");

    $this->addForeignKey("fk_mapt_sort", "{{%mappingtable}}", "crudsort_id", "{{%mappingfield}}", "id", "NO ACTION", "NO ACTION");

    $this->addForeignKey("fk_mapt_mast", "{{%mappingtable}}", "crudmaster_id", "{{%mappingtable}}", "id", "NO ACTION", "NO ACTION");

I first chooses RESTRICT and CASCADE as contraints but got sql errors so I thought that NO ACTION may help me

I generated model and crud by gii and got Database Exception on going to index.php?r=mappingtable%2Findex :

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'mappingtable'

The SQL being executed was: SELECT COUNT(*) FROM `mappingtable` LEFT JOIN `mappingtable` ON `mappingtable`.`crudmaster_id` = `mappingtable`.`id` LEFT JOIN `mappingfield` ON `mappingtable`.`crudsort_id` = `mappingfield`.`id` WHERE (`mappingtable`.id LIKE "%%") AND (`mappingfield`.id LIKE "%%")

So I modified MappingtableSearch.php (at the end the $query->joinWith([‘crudmaster’ thing) :

public function search($params)

{

    $query = Mappingtable::find();

    $dataProvider = new ActiveDataProvider([

        'query' => $query,

    ]);


$dataProvider->setSort([

    'attributes' => [

        'id',

    ],

    'defaultOrder'=>['id' => SORT_ASC],

]);


//if (!($this->load($params) && $this->validate())) {

if (isset($_GET['MappingtableSearch']) && !($this->load($params) && $this->validate())) {

    $query->joinWith(['crudmaster']);

    $query->joinWith(['crudsort']);

    return $dataProvider;

}


    $query->andFilterWhere([

        'id' => $this->id,

        'crudsort_id' => $this->crudsort_id,

        'crudmaster_id' => $this->crudmaster_id,

        'crudnumbers' => $this->crudnumbers,

        'createtime' => $this->createtime,

        'createuser_id' => $this->createuser_id,

        'updatetime' => $this->updatetime,

        'updateuser_id' => $this->updateuser_id,

    ]);


    $query->andFilterWhere(['like', 'name', $this->name]);


    $query->joinWith(['crudmaster' => function ($q) {

        $q->from('mappingtable p');

        $q->where('{{%mappingtable}}.id LIKE "%' . $this->crudmasterId . '%"');

    }]);


    $query->joinWith(['crudsort' => function ($q) {

        $q->where('{{%mappingfield}}.id LIKE "%' . $this->crudsortId . '%"');

    }]);

    return $dataProvider;

}

Now I have no error and I create a record in Mappingtable (only filled the name) - it creates the record but when I come back to index the record does not show up !

I think that my modification was not correct or not enough

I had a look at the query generated :

SELECT `mappingtable`.* FROM `mappingtable` LEFT JOIN `mappingtable` `p` ON `mappingtable`.`crudmaster_id` = `p`.`id` LEFT JOIN `mappingfield` ON `mappingtable`.`crudsort_id` = `mappingfield`.`id` WHERE (`mappingtable`.id LIKE "%%") AND (`mappingfield`.id LIKE "%%")

In phpmyadmin this gives no record too but if I try :

SELECT `mappingtable`.* FROM `mappingtable` LEFT JOIN `mappingtable` `p` ON `mappingtable`.`crudmaster_id` = `p`.`id` LEFT JOIN `mappingfield` ON `mappingtable`.`crudsort_id` = `mappingfield`.`id` WHERE (`mappingtable`.id LIKE "%%") 

the records shows up. I wonder why the where clause contains "AND (mappingfield.id LIKE "%%")" !?

Please if you have any hint let me know ! - Thanks