Cakephp find order by then group by

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Cakephp find order by then group by

anwar korti

Hello In a Message controller I try to display the latest message of each user (sender) for a connected one (receiver)  I try this but I still find the first entry. Thank you for your help

Messages table:

  • id
  • user_id -> Sender
  • receiver_id -> receiver
  • content 
  • created -> datetime
  • statut -> read or not

$this->set('messages', $this->Message->find('all',
array
(
'conditions' => array('Message.receiver_id'=>$user),
'order' => array('Message.created desc'),
'group'=>'Message.user_id')));

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Jonathan Sundquist
The group by is throwing it off.  When you use a group and an order together you typically do not get the expected result. In order to get the desired results you might need to do an inner select.  I don't remember the correct way to do it off the top of my head though to get what you are looking for.  I do know though that the group by clause is what is causing your issues.

On Thu, Oct 18, 2012 at 4:47 AM, anwar korti <[hidden email]> wrote:

Hello In a Message controller I try to display the latest message of each user (sender) for a connected one (receiver)  I try this but I still find the first entry. Thank you for your help

Messages table:

  • id
  • user_id -> Sender
  • receiver_id -> receiver
  • content 
  • created -> datetime
  • statut -> read or not


$this->set('messages', $this->Message->find('all',
array
(
'conditions' => array('Message.receiver_id'=>$user),
'order' => array('Message.created desc'),
'group'=>'Message.user_id')));

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Jeremy Burns : Class Outfit
In reply to this post by anwar korti
You are doing a find all with a desc sort on created, so in theory you're going to get back multiple rows (messages). Don't you want to do a find first with the same order? Then you'd get back the latest message. Also, if it's a single row you expect back it's probably more correct to call the variable $latestMessage, rather than $messages (which suggest plural).

Jeremy Burns
Class Outfit

http://www.classoutfit.com

On 18 Oct 2012, at 10:47:52, anwar korti <[hidden email]> wrote:

Hello In a Message controller I try to display the latest message of each user (sender) for a connected one (receiver)  I try this but I still find the first entry. Thank you for your help

Messages table:


  • id
  • user_id -> Sender
  • receiver_id -> receiver
  • content 
  • created -> datetime
  • statut -> read or not

$this->set('messages', $this->Message->find('all',
array
(
'conditions' => array('Message.receiver_id'=>$user),
'order' => array('Message.created desc'),
'group'=>'Message.user_id')));

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Shiv Shankar
In reply to this post by anwar korti
First of of try to do each and everything using arrays wherever possible. It will maintain the spirit of CakePHP. Use following code :

$this->Message->find('all',
    array(
        'conditions' => array('Message.receiver_id'=>$user),
        'order' => array('Message.created' => 'Desc'),
        'group' => array('Message.user_id'),
        'field' => array('Message.user_id',...........)
    )
)

We use group by only if we have to perform some group function like sum, count etc...and fields option is compulsory.




On Thursday, 18 October 2012 15:17:53 UTC+5:30, anwar korti wrote:

Hello In a Message controller I try to display the latest message of each user (sender) for a connected one (receiver)  I try this but I still find the first entry. Thank you for your help

Messages table:

  • id
  • user_id -> Sender
  • receiver_id -> receiver
  • content 
  • created -> datetime
  • statut -> read or not

$this->set('messages', $this->Message->find('all',
array
(
'conditions' => array('Message.receiver_id'=>$user),
'order' => array('Message.created desc'),
'group'=>'Message.user_id')));

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Shiv Shankar
sorry use 'fields' option not field

On Thursday, 18 October 2012 18:32:14 UTC+5:30, Shiv Shankar wrote:
First of of try to do each and everything using arrays wherever possible. It will maintain the spirit of CakePHP. Use following code :

$this->Message->find('all',
    array(
        'conditions' => array('Message.receiver_id'=>$user),
        'order' => array('Message.created' => 'Desc'),
        'group' => array('Message.user_id'),
        'field' => array('Message.user_id',...........)
    )
)

We use group by only if we have to perform some group function like sum, count etc...and fields option is compulsory.




On Thursday, 18 October 2012 15:17:53 UTC+5:30, anwar korti wrote:

Hello In a Message controller I try to display the latest message of each user (sender) for a connected one (receiver)  I try this but I still find the first entry. Thank you for your help

Messages table:

  • id
  • user_id -> Sender
  • receiver_id -> receiver
  • content 
  • created -> datetime
  • statut -> read or not

$this->set('messages', $this->Message->find('all',
array
(
'conditions' => array('Message.receiver_id'=>$user),
'order' => array('Message.created desc'),
'group'=>'Message.user_id')));

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

lowpass
In reply to this post by anwar korti
As Jeremy said, use find('first') because you only want one message.
And Jonathon is correct -- drop the group option.


On Thu, Oct 18, 2012 at 5:47 AM, anwar korti <[hidden email]> wrote:

> Hello In a Message controller I try to display the latest message of each
> user (sender) for a connected one (receiver)  I try this but I still find
> the first entry. Thank you for your help
>
> Messages table:
>
> id
> user_id -> Sender
> receiver_id -> receiver
> content
> created -> datetime
> statut -> read or not
>
> $this->set('messages', $this->Message->find('all',
> array(
> 'conditions' => array('Message.receiver_id'=>$user),
> 'order' => array('Message.created desc'),
> 'group'=>'Message.user_id')));
>
> --
> 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 [hidden email].
> To unsubscribe from this group, send email to
> [hidden email].
> 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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Vanja Dizdarević
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Jeremy Burns : Class Outfit
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ć <[hidden email]> 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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Vanja Dizdarević
Well, the original question states:

display the latest message of each user (sender) for a connected one (receiver)

Question might be vague, but this led me to believe that he refers to the receiver => current user, senders => all other users, latest message => last message of each of the senders.

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

euromark
In reply to this post by Jeremy Burns : Class Outfit
I remember that I ran into this issue at least 3 times in the past
and solved it this way:

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ć <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="vKb9fOVDBU0J">lex.non...@...> 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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="vKb9fOVDBU0J">cake...@....
To unsubscribe from this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="vKb9fOVDBU0J">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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Vanja Dizdarević
@euromark, that works, but is it really safe?

To best of my knowledge and experience with MySQL, using MAX (and other aggregate friends) with other fields in the same query does not necessarily ensure that other fields will correspond to the same row! Correct me if I'm wrong, but this is how I interpreted the MySQL documentation.

For instance SELECT id, MAX(price), product_type .... GROUP BY (product_type) is not a 100% way to get the ID of the record with the highest price. Which is odd to me, but I've been tackling this problem for ages now and this example made me question this approach.

--
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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
 
 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

lowpass
In reply to this post by Vanja Dizdarević
The OP did say that but then the example given was for a single user.

On Fri, Oct 19, 2012 at 5:34 AM, Vanja Dizdarević
<[hidden email]> wrote:

> Well, the original question states:
>
>
>> display the latest message of each user (sender) for a connected one
>> (receiver)
>
>
> Question might be vague, but this led me to believe that he refers to the
> receiver => current user, senders => all other users, latest message => last
> message of each of the senders.
>
> --
> 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 [hidden email].
> To unsubscribe from this group, send email to
> [hidden email].
> 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 [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en.


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Cakephp find order by then group by

Shamshad Akther Khan
In reply to this post by Vanja Dizdarević
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Loading...