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:
-- 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 MessageRIGHT JOIN (SELECT MAX(created) as last_created, user_id FROM messages GROUP BY user_id)as latestON Message.created = latest.last_created AND Message.user_id = latest.user_idGROUP BY Message.user_idORDER 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 cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at https://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
No comments:
Post a Comment