Seemingly simple task, but not quite, eh...
Solving this with a single query is possible, but you would have to do a
manual query (with Model::query), something like:
SELECT Message.* FROM messages as Message
RIGHT JOIN (SELECT MAX(created) as last_created, user_id FROM messages
GROUP BY user_id)
as latest
ON Message.created = latest.last_created AND Message.user_id =
latest.user_id
GROUP BY Message.user_id
ORDER BY Message.created DESC;
You cannot instruct MySQL how to decide which row to use when grouping
(yeah, i know...), so you would have to use MAX. The problem with this
query is that you get ambiguous results if you happen to have 2 messages
with same timestamp, user_id, receiver_id, so you would somehow need to
sort by created and Message.id too.
Complex queries can become expensive quickly, so it's maybe better to find
last message separately for each user.
$senders = $this->Message->find->('all', array(
'fields' => array(
'DISTINCT Message.user_id'
)
'conditions'=>array(
'receiver_id' => $user_id
)));
$newMessages = array();
foreach($senders as $sender) {
$newMessages[] = $this->Messages->find('first', array(
'conditions' => array('user_id' => $sender['Message']['user_id'],
'receiver_id' => $user_id),
'order'=>array('created'=>'desc', 'id'=>'desc')
));
}
Another crazy idea is to create a *Message.last* boolean column and update
this field when you insert a new record:
- run "UPDATE messages SET last = 0 WHERE user_id = $user_id AND
receiver_id=$receiver_id";
- Create new message with Message.last = 1
... and then get last messages with find-all query with condition
Message.last = 1.
I wished a thousand times that MySQL had a way of doing this properly in a
single query.
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
