CODEWITHSUNDEEP
phpdatabaseyiiyii2migration
GVS
GVS

Reputation: 31

Yii2 Migration move data to other table

I have table:

 CREATE TABLE `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` int(11) DEFAULT NULL,
  `status` NOT NULL DEFAULT,
  `date_created` datetime NOT NULL,
  `date_used` datetime DEFAULT NULL,
  `used_by_user_id` int(11) DEFAULT NULL,) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and I have created table 

        $this->createTable('used', [
        'id'              => $this->primaryKey(),
        'ticket_id'        => $this->integer()->notNull(),
        'date_used'        => $this->integer()->notNull(),
        'used_by_user_id' => $this->integer()->notNull(),
], 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB');

I want to move some data(date_used & used_by_user_id) from table ticket to other table used with migration yii2 in the query. But I don't know how to do it without ActiveRecord & array.

Upvotes: 1

Views: 1839

Answers (2)

Radon8472
Radon8472

Reputation: 4951

First you should build your select-query containing all values you need for the new table.

Then you can pass the Query-object into the insert method. E.g. like this:

$dataQuery = (new yii\db\Query)
  ->select(['ticket_id' => 'id', 'date_used', 'used_by_user_id'])
  ->from('ticket')
  // ->where() // add conditions here if you dont like to copy all 
  ;

$this->insert('used', $dataQuery);

Take a look at the first col in the select:

  • if you pass key/value pairs, the Key will be the name that is used in the insert-command, and the value is the name that will be used in the select statement

This is very important to know if the column names in your target table are not identical to your source table.

p.s. you can also use any model queries as dataQuery e.g.

$this->insert('used', Ticket::find());

Read more at https://github.com/yiisoft/yii2/issues/13807

Upvotes: 0

GVS
GVS

Reputation: 31

$this->execute("
INSERT INTO used (ticket_id, date_used, used_by_user_id) 
SELECT id, date_used, used_by_user_id 
FROM ticket 
WHERE 
used_by_user_id IS NOT NULL AND date_used IS NOT NULL
");

Only sql. Yii-migration not supported select in the insert into

Upvotes: 2

Related Questions