Building Complex Queries

Yii 2.0 General Discussions
1

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.

2

From the docs:

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.

I think that it should be:

->leftJoin(['clients_price_list ' => $subQuery], 'lst_pilot_car_items.PilotCarItemId = clients_price_list.PilotCarItemId')

I also think that you should use \yii\db\Expression when using functions in your SELECT clause:

->select(new \yii\db\Expression('COALESCE(clients_price_list.UnitPrice, lst_pilot_car_items.UnitPrice) AS Price'))
3

you can simply the query like this

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

and the query may be like this

(new Query())->select('*')
	->from('lst_pilot_car_items t2')
	->leftJoin('clients_price_list t1', 't1.PilotCarItemId = t2.PilotCarItemId')
	->where(['appy filter here']);
4

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.

1 Like
5

This is because Yii will try to quote fields when you don’t wrap your statement in a \yii\db\Expression :wink:

Advance on your devs, and when you get free time, make sure to come back to this issue and we’ll fix it all together.