Fetch data for a select query in yii migrations

Question

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>'],
                    .
                    .
                    .
                ]
            );

Is there a reason why you don't want to create records in parent table using ActiveRecord models? You can create parent records in for/foreach cycle then use something like `$model->id` to get parent id for batch inserting child records. — Michal Hynčica, May 11 '21 at 17:54
@MichalHynčica: I want to create a migration script so that in future if we are to migrate the whole database these values are inserted directly in one single migration rather than creating a script using foreach. As the records are static it would be just a one time script anyway. — Rebecca, May 11 '21 at 19:29

Muhammad Omer Aslam · Accepted Answer · 2022-02-26T23:33:52.917

2

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

edited Feb 26 '22 at 23:33

answered May 11 '21 at 16:46

Muhammad Omer Aslam

22,976
9
42
68

2

It might be better to use `$this->db` component instead of `Yii::$app->db` to make sure you are running your query on same DB. – Michal Hynčica May 11 '21 at 17:50

Fetch data for a select query in yii migrations

1 Answers1