Thankz a lot sir, Your procedure has helped me solve a problem in live
tracking issue....
On Friday, October 19, 2012 at 2:41:18 PM UTC+6, Vanja Dizdarević 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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
