I’m trying to replicate the following SQL Statement in Yii, but am struggling and hoping someone can help me get the proper syntax
This is the functional SQL Statement.
SELECT t2.PilotCarItemId, t2.PilotCarItem, COALESCE(t1.UnitPrice, t2.UnitPrice) AS Price
FROM lst_pilot_car_items As t2 LEFT JOIN (
SELECT * FROM clients_price_list WHERE ClientId=1 AND PilotCarItemId=1
) As t1 ON t2.PilotCarItemId=t1.PilotCarItemId
WHERE t2.PilotCarItemId=1
This is one of my attempts. I thought I could build the subquery first and then build the main query and join the subquery to it, but can’t seem to make it work.
In another attempt I even tried applying the aliasing, but that failed miserably. If someone can show me this, I’d love to learn the proper way.
$subQuery = (new \yii\db\Query())
->select('UnitPrice')
->from('clients_price_list')
->where(['clients_price_list.PilotCarItemId' => $item])
->andWhere(['clients_price_list.ClientId' => $client]);
// $command = $subQuery->createCommand()->sql;
// echo $command.'<br>';
// $row = $subQuery->one();
// echo $subQuery->count();
// echo $row->UnitPrice; //This errs? "Trying to get property of non-object"
$query = (new \yii\db\Query())
->select('COALESCE(clients_price_list.UnitPrice, lst_pilot_car_items.UnitPrice) AS Price')
->from('lst_pilot_car_items')
->leftJoin($subQuery, 'lst_pilot_car_items.PilotCarItemId = clients_price_list.PilotCarItemId')
->where(['lst_pilot_car_items.PilotCarItemId' => $item]);
// $command = $query->createCommand()->sql; //Errs? "strpos() expects parameter 1 to be string, array given"
// echo $command.'<br>';
$row = $query->one(); //Errs? "strpos() expects parameter 1 to be string,
echo $query->count().'<br>';
echo $row->price;
I’ve review the documentation I could find, but all the example are very basic. If anyone knows of more complex example please feel free to pass along a link.
Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a yii\db\Query object representing the sub-query while the corresponding key represents the alias for the sub-query.
select *
from lst_pilot_car_items t2
left join clients_price_list t1 on t1.PilotCarItemId = t2.PilotCarItemId
where
(t1.ClientId = 1 and t1.PilotCarItemId = 1)
and t2.PilotCarItemId = 1
I don’t know what I’m doing wrong, but I cannot get either proposed solutions to work.
For instance, if I used (I can’t use * as I need the result of Coalesce as my output)
->select("COALESCE(clients_price_list.UnitPrice, lst_pilot_car_items.UnitPrice) AS Price")
the output SQL is SELECT COALESCE(clients_price_list.UnitPrice, `lst_pilot_car_items`.`UnitPrice)` AS `Price
pay attention to the UnitPrice) where Yii has included the ) in the field name quotes?
I keep getting a MySQL error relating to “Unknown column ‘clients_price_list.UnitPrice’ in ‘field list’”, but the table and field are correct! I can do SELECT UnitPrice From clients_price_list and it works just fine?
Anyways, I’ve already spent about 2 days on this and not moved forward at all, so I’m going to say Yii defeated me on this and move on. I at least have the raw SQL Statement working fine, it will have to do.
Thank you both for trying to help. Truly appreciated.