Hi,
I have this command that works just fine when I order by any other column except base_price and final_price.
When I order by any of the two it returns null values for both of them.
I did a $command->getText(), run it in mysql workbench and it works just fine.
Can anyone help me with this?
$select = <<<SQL
:_start_date as start_date,
@numberDays := DATEDIFF(:_end_date,:_start_date) as days,
@basePrice := @numberDays * pr.price_per_day as base_price,
case
when @numberDays < 2 then CAST(@basePrice as decimal(4,2))
when @numberDays < 5 then CAST(@basePrice * (100 - pr.days_2_discount) / 100 as decimal(4,2))
when @numberDays < 10 then CAST(@basePrice * (100 - pr.days_5_discount) / 100 as decimal(4,2))
when @numberDays < 20 then CAST(@basePrice * (100 - pr.days_10_discount) / 100 as decimal(4,2))
when @numberDays < 30 then CAST(@basePrice * (100 - pr.days_20_discount) / 100 as decimal(4,2))
else CAST(@basePrice * (100 - pr.days_30_discount) / 100 as decimal(4,2))
end as final_price,
pr.valid_from as pr_start_date,
pr.valid_to as pr_end_date,
p.*
SQL;
$command = Yii::app()->db->createCommand();
$command->select($select)->
from('price pr')->
leftJoin('parking p', 'p.id = pr.parking_id')->
leftJoin('location l', 'p.location_id = l.id')->
leftJoin('supplier s', 's.id = p.supplier_id')->
where('pr.status = 1 AND p.status = 1 AND s.status = 1 AND l.id = :_location_id')->
having('start_date BETWEEN pr_start_date AND pr_end_date')->order('final_price asc');
$command->params = [
':_start_date' => $startDate,
':_end_date' => $endDate,
':_location_id' => $locationId
];