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.

Does this help? http://stackoverflow.com/questions/8467698/sub-queries-activerecord-yii — András Szepesházi, Mar 30 '12 at 11:43
Interesting, so reading that link looks like is not possible to do it using CDbCriteria. Thanks! — Puigcerber, Mar 30 '12 at 14:02

score 14 · Answer 1 · answered Oct 07 '12 at 11:56

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.

score 5 · Accepted Answer · answered Mar 30 '12 at 23:48

5

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.

answered Mar 30 '12 at 23:48

deepwell

20,195
10
33
39

1

So as looks it isn't possible to do it using CDbCriteria and this is the only answer, I'm accepting it. Thanks. – Puigcerber Apr 04 '12 at 14:46
FWs, especially Yii (I'm working with it in company) have many discomfort impossibilities like Models with bugs, sessions in Yii, for example, can't save values in associative multi-dimensional array. That is why I set one point up either. – Arthur Kushman Jun 20 '13 at 10:52
2

The question was not about what's the best practice for decently complex sql query. – Keeper Hood Sep 19 '13 at 09:01

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

2 Answers2