[FIXED] Yii1 MYSQL Join 4 Tables in where class and group

Issue

I am new to YII1 and I want to query one database

I have database for delivery of items to customers. for this I have 4 tables,

delivery_logs 
delivery
driver
customer

(we have delivery log table cause some time one delivery have many entries like not delivered & other driver take delivery etc)

delivery_log

id delivery_id driver_id  message     date_added
1  1           1         OK         2016-09-13 17:38:15 
2  2           2         OK         2016-09-13 17:35:18 
3  1           1         Not OK     2016-09-13 17:33:10     
4  1           3         OK         2016-09-13 17:32:13 
5  2           4         waiting    2016-09-13 17:20:11 


delivery
delivery_id  customer_id name          status
1            1           delivery 1    done
2            1           delivery 2    done
3            2           delivery 3    done


driver
driver_id  name
1          driver1
2          driver2
3          driver3

customer
customer_id  first_name   last_name
1            name1          other name1
2            name2          other name2
3            name3          other name3
4            name3          other name4

now I want to have query of

last 1 record (order by date_added) from delivery_log 
for each delivery by each driver

where delivery= done and I want to have bellow fields

delivery_log.delivery_id  delivery_log.driver_id delivery_log.message date_added 
delivery.name driver.name 
customer.first_name customer.last_name

I am using

$criteria=new CDbCriteria;

it's working and getting the last record with bellow

$criteria->order = 't.date_added DESC';

but when I put

$criteria->group = 't.delivery_id,t.driver_id';
$criteria->order = 't.date_added DESC';

this did't return last record....

Thanks for all help.

Solution

for complex query related to a model you can use findAllBySql where you use a flat sequel for retrive the models you need

$sql = 'SELECT colA, colB 
        FROM any_table as a
        INNER JOIN inner_join_table as b on  a.key = b.key
        LEFT JOIN  left_join_table as c on a.key = c.key
        WHERE a.col1 = .....'
$model = MyModel::model()->findAllBySql($sql);

using this kind of function you can eg::

$sql = 'select 
         delivery_log.delivery_id,
         delivery_log.driver_id,
         delivery_log.message, 
         delivery_log.date_added,
         delivery.name,
         driver.name,
         customer.first_name,
         customer.last_name
         from delivery_log
         inner join delivery on delivery_log.key = delivery.key 
         .... 
         ...'
$model= DeliveryLog::model()->findBySql($sql);

Answered By - ScaisEdge