Yii: how to ORDER BY before GROUP BY with CDbCriteria?

Question

With a query like this one:

SELECT * FROM (
    SELECT * FROM message ORDER BY added_on DESC
) as m WHERE receiver_id = 2 GROUP BY sender_id ORDER BY priority_id DESC;

I know how to do it using findAllBySql:

$this->findAllBySql(
    'SELECT * FROM (
        SELECT * FROM message ORDER BY added_on DESC
     ) as m WHERE receiver_id = :receiverId 
     GROUP BY sender_id 
     ORDER BY priority_id DESC', 
     array('receiverId' => $userId));

But I was wondering if there is any way to do this using CDbCriteria cause the following code, of course, doesn't work:

$criteria = new CDbCriteria();
$criteria->condition = 'receiver_id = :receiverId';
$criteria->group = 'sender_id';
$criteria->order = 'priority_id DESC, added_on DESC';
$criteria->params = array('receiverId' => $userId);

Thanks.

Answer 1

I know it's too late. I had similar problem, and I try to approach like this

$criteria = new CDbCriteria();
$criteria->select = '*, MAX(added_on) max_added_on';
$criteria->condition = 'receiver_id = :receiverId';
$criteria->group = 'sender_id';
$criteria->order = 'priority_id DESC, max_added_on DESC';
$criteria->params = array('receiverId' => $userId);

It solved my problem. If you want to retrieve max_added_on, you just have to add another property on model class.

Answer 2

If you have a decently complex SQL query, it's best practice to keep it in SQL instead of writing it in Active Record. Yes it limits database portability, but you probably don't have a tun of these queries, and simpler and more maintainable code always wins.