Yii2 Wrong active record Query syntax in debug toolbar

Question

I made a query for active records and does not work for my data query my grid view is empty. I try to do it manually, the result is ok. and i found and I found the cause of the problem, for compare about the wrong sql query see code below.

active record query :

    $query-> innerJoinWith(['askPos'])
          ->andwhere(['ask_policy.id'=>'ask_po.id_policy'])
          ->innerjoinWith(['client'])->andWhere(['id_client'=>Yii::$app->user->identity->id_client])->all();



**in yii debug show me sql  : **
`SELECT `ask_policy`.* FROM `ask_policy` INNER JOIN `ask_po` ON `ask_policy`.`id` = `ask_po`.`id_policy` INNER JOIN `ask_client` ON `ask_policy`.`id_client` = `ask_client`.`id` WHERE (`ask_policy`.`id`='ask_po.id_policy') AND (`id_client`=930);`

above code not work. the problem is

`ask_policy`.`id`='ask_po.id_policy'

**It should be : **

SELECT `ask_policy`.`policy_num`, `ask_po`.po_num, ask_client.insuranced_name FROM `ask_policy`  INNER JOIN `ask_po` ON `ask_policy`.`id` = `ask_po`.`id_policy`  INNER JOIN `ask_client`  ON `ask_policy`.`id_client` = `ask_client`.`id`  WHERE (`ask_policy`.`id`=`ask_po`.`id_policy`) AND (`id_client`=930);

or active record query like this below :

$query->innerjoinWith(['client'])->where(['id_client'=>Yii::$app->user->identity->id_client])
           ->innerJoinWith(['askPos'])
            ->andWhere(['ask_policy.id'=>'id_policy'])->all();

in yii debug show me sql :

SELECT `ask_policy`.* FROM `ask_policy` INNER JOIN `ask_client` ON `ask_policy`.`id_client` = `ask_client`.`id` INNER JOIN `ask_po` ON `ask_policy`.`id` = `ask_po`.`id_policy` WHERE (`id_client`=930) AND (`ask_policy`.`id`='ask_po.id_policy')

**it should be : **

SELECT `ask_policy`.`policy_num`, `ask_po`.po_num, ask_client.insuranced_name FROM `ask_policy` 
INNER JOIN `ask_client` ON `ask_policy`.`id_client` = `ask_client`.`id` 
INNER JOIN `ask_po` ON `ask_policy`.`id` = `ask_po`.`id_policy` 
WHERE (`id_client`=930) AND (**`ask_policy`.`id`=`id_policy`**)

why the above code can be different from manual sql query? did I make a mistake in using it?

Irfan Ali Irfan Ali · Accepted Answer · 2017-04-08T03:24:34

Update your query on andWhere condition. Don't pass array here instead of that pass string like below.

 $query-> innerJoinWith(['askPos'])
      ->andwhere("ask_policy.id = ask_po.id_policy")
      ->innerjoinWith(['client'])->andWhere(['id_client'=>Yii::$app->user->identity->id_client])->all();

Yii2 Wrong active record Query syntax in debug toolbar

1 Answers