我试图使用Group by in find方法为我的关系数据库,我想获得最新的记录与唯一的receiver_id的结果集由user_id过滤,下面是我的代码:
$this->loadModel('Chat');
$lastChat = $this->Chat->find('all', array(
'conditions' => array(
'Chat.user_id' => $user_id['User']['id']
),
'fields' => array(
'Chat.id',
'Chat.chat',
'Chat.user_id',
'Chat.receiver_id',
'Chat.read',
'Chat.created'
),
'group' => array('Chat.receiver_id'),
'order' => array('Chat.created DESC')
));
然而,这似乎不起作用。我不知道为什么,但我只得到一个结果…
如何使用上述规则获得多个结果
尝试如下:
$db = $this->Chat->getDataSource();
$chats = $db->query("SELECT * , (Chat.user_id + Chat.receiver_id) AS dist
FROM (
SELECT *
FROM chats t
WHERE ( t.user_id =$id OR t.receiver_id =$id )
ORDER BY t.id DESC
) Chat
GROUP BY dist
ORDER BY Chat.id DESC");