[SOLVED] Fetch data for a select query in yii migrations

Fetch data for a select query in yii migrations

I am creating a parent child table using yii migration where there is a foreign key on the child table. I tried using execute but that did not help me fetching the data needed. Is there a way I can query the parent table and insert the parent id on the child table as a foreign key? Something like fetch()/fetchAll() that is used inside phinx migrations.

    $this->batchInsert('{{%parent_table}}',
                ['name'],
                [
                    ['P1'],
                    ['P2'],
                    ['P3'],
                    ['P4']
                ]
            );
    $a = $this->execute("select * from parent_table where name = 'P1'");
    // get the data from the table that will get me the id from the above query.

    $this->batchInsert('{{%child_table}}',
                [['name'],['parent_id']],
                [
                    ['C1','<parent id>'],
                    ['C2','<parent id>'],
                    .
                    .
                    .
                ]
            );

Solution

You need to use

$sql = <<<SQL
select * from parent_table where name = 'P1'
SQL;

$res = Yii::$app->db->createCommand($sql)->queryAll();

The $this->execute() is used to execute sql statements like update or delete instead

Edit

Better use $this->db instead of Yii::$app->db to make sure you are running on the same db, as mentioned in the comments