CdbCommand issue

Yii 1.1 General Discussion
1

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

            ];
2

Frankly, with my knowledge I cannot understand the reason. But still could you try if this is working?

$query=whatever query which is working ok in MySQL testbench.




Yii::$app->db->createCommand($query)->queryAll();
3

Unfortunately the same query that works in mysql workbench returns null in Yii.