Cakephp使用groupby并获得最高ID

I'm using below query to get the unique result for receiver_id but also trying to get the highest id returned only rather than lowest one which is what below find query is generating. Is there a way we can change the find optional parameters to get highest id returned only?

我正在使用下面的查询来获取receiver_id的唯一结果，但也试图获得仅返回最高的id而不是最低的id，这是查找以下查询生成的内容。有没有办法我们可以更改find可选参数以获得仅返回的最高ID？

$this->loadModel('Chat');
        $lastChat = $this->Chat->find('all', array(
            'conditions' => array(
                'Chat.user_id' => $user_id['User']['id']
            ),
            'order' => array('Chat.id DESC'),
            'fields' => array(
                'Chat.id',
                'Chat.chat',
                'Chat.user_id',
                'Chat.receiver_id',
                'Chat.read',    
                'Chat.created'
            ),
            'group' => array('Chat.receiver_id')
        ));

2 个解决方案

#1

I would say it only makes sense to use a GROUP BY clause in SQL if you are going to use an aggregate function in the SELECT clause.

我想说如果要在SELECT子句中使用聚合函数，只在SQL中使用GROUP BY子句才有意义。

Maybe I'm not understanding the nature of your problem, but would this query work for you?

也许我不理解你问题的本质，但这个查询对你有用吗？

SELECT Chat.receiver_id, MAX(Chat.id)
FROM chats Chat
WHERE Chat.id = X
GROUP BY Chat.receiver_id

If that's the case you just need to add an extra element in the fields array:

如果是这种情况，您只需要在fields数组中添加一个额外的元素：

$this->Chat->find('all', array(
  'conditions' => ...,
  'order' => ...,
  'fields' => array(..., 'MAX(Chat.id) as max_id',...),
  'group' => ...,
);

#2

This is one way to achieve what you're after.

这是实现您所追求的目标的一种方式。

$this->loadModel('Chat');
$lastChat = $this->Chat->find('all', array(
        'conditions' => array(
        'Chat.user_id' => $user_id['User']['id']
        ),
        'order' => array('Chat.id DESC'),
        'fields' => array(
            'Chat.id',
            'Chat.chat',
            'Chat.user_id',
            'Chat.receiver_id',
            'Chat.read',    
            'Chat.created'
        ),
        'order' => array('Chat.receiver_id DESC'),
        'limit' => 1
    ));

There's no need to use GROUP BY. Just use ORDER BY and LIMIT.

没有必要使用GROUP BY。只需使用ORDER BY和LIMIT。

#1