Re: Cakephp find order by then group by

I remember that I ran into this issue at least 3 times in the past

and solved it this way:

http://stackoverflow.com/questions/8707048/last-x-blog-entries-but-only-once-per-user

a subquery that gets the results first. otherwise you will not be able to sort (since grouping happens prior to the sort and loses the correct results).

Am Freitag, 19. Oktober 2012 10:47:58 UTC+2 schrieb Jeremy Burns:

That's way over the top....surely?

In simple terms, you want to get a single message with the latest created date where the receiver is a given $userId. So this would do it:

$message = $this->Message->find(

'first',

array(

'conditions' => array('Message.receiver_id' => $userId),

'order' => array('Message.created' => 'desc')

)

);

No?

Jeremy Burns

Class Outfit

http://www.classoutfit.com

On 19 Oct 2012, at 09:41:18, Vanja Dizdarević <lex.non...@gmail.com> wrote:

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

--
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.