Re: Cakephp find order by then group by

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 cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.