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