How do you translate these request into Yii orm commands?

Yii 2.0 General Discussions
1

The documentation doesn’t give enough information for edge cases where we have weird strings, sql function, and what they expect inside the arrays we pass as params, so I was wondering what would be the correct commands for the following examples:


        $request = "SELECT `game_data`.`country_code` AS country

            FROM currencies

            INNER JOIN game_data ON  `currencies`.`country_id` =  `game_data`.`countries_id`

            WHERE `currencies`.`currency` = '$currency' AND `currencies`.`sale_id` = '$sale_id'";


        $result = app()->db->createCommand($request)->queryAll();


		


        $result = $db->createCommand("INSERT INTO `sales_data` (`group_id`, `cap_list`, `dll_name`) VALUES ({$gameID}, '[\"{$name}\"]' ,  '{$name}')")->execute();





		


        $result = $db->createCommand('INSERT INTO `prio_data` (`group_id`, `consumer_name`, `priority`, `tax_code`) VALUES '.implode(',', $values))->execute();








        $request = "SELECT  `consumer_id` AS  `id` , LOWER(  `consumer_code` ) AS  `consumer`

            FROM  `global_consumer`";

            $result = $this->dbh->createCommand($request)->queryAll();
2

General info: Database Access Objects and Query Builder

For the #1 see:

  • select()

  • from()

  • join()

  • where()

For the #2 - I’m not really sure what’s the deal with fancy values (quoting?) here but you should see:

  • non select queries

  • quoting table and column names

  • insert()

For the #3 - I’m guessing this is batch insert in disguise, see:

  • non select queries

  • batchInsert()

For the #4 see:

  • select() 4th example

  • from()

So as you can see I disagree with “The documentation doesn’t give enough information for edge cases where we have weird strings, sql function, and what they expect inside the arrays we pass as params”.

3

Thanks, do you know how to make an update statement with multiple where conditions?


                $this->localdb->createCommand()

                ->update(

                    $this->MYTable,

                    [

                        'name' => $el['new'],

                        'data' => $el['data'],

                    ],

                    [

                        'userId', $this->user,

                        'product_id', $this->productId,

                        'name', $el['old'],

                        'created', $el['date'],

                        'category', $el['cat'],

                    ]


                );

The following doesn’t work for some reason.

4

You are not setting conditions properly. See the link for where() in my previous post.