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ć
> <[email protected]<javascript:>>
> 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 [email protected]<javascript:>
> .
> To unsubscribe from this group, send email to
> [email protected] <javascript:>.
> 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 [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.
