php Yii：如何在使用 CDbCriteria 的 GROUP BY 之前进行 ORDER BY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9939275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Yii: how to ORDER BY before GROUP BY with CDbCriteria?
提问by Puigcerber
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:
我知道如何使用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 CDbCriteriacause the following code, of course, doesn't work:
但我想知道是否有任何方法可以使用CDbCriteria执行此操作，导致以下代码当然不起作用：
$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.
谢谢。
采纳答案by deepwell
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.
如果您有一个相当复杂的 SQL 查询，最好将它保存在 SQL 中，而不是将它写在 Active Record 中。是的，它限制了数据库的可移植性，但您可能对这些查询一无所知，而且更简单、更易于维护的代码总是胜出。
回答by peculiar
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.
它解决了我的问题。如果您想检索 max_ added_on，您只需在模型类上添加另一个属性。
