0

I am attempting to get a list of messages, by grouping the from_user_id and to_user_id so they look like the screenshot below. Only, my problem is they do not seem to be ordering by the most recent.

Here's a screenshot showing how they look:

enter image description here

Query: select 

select 
concat(to_user_id, from_user_id) as group_by,
pm.*
from personal_messages pm
where (to_user_id = 1265) or (from_user_id = 1265)
group by group_by
order by id desc

Table Structure:

enter image description here

Ben
  • 5,627
  • 9
  • 35
  • 49
  • 1
    Since you tagged Laravel, why don't you try with eloquent? – Jean-Philippe Murray Apr 07 '17 at 13:49
  • @Jean-PhilippeMurray I am using Eloquent in my application, but using normal SQL queries to try and get the best outcome and then I'll convert that to Eloquent :) – Ben Apr 07 '17 at 14:27
  • I don't know your exact structure, but `PersonalMessage::where('to_user_id', 1265)->orWhere('from_user_id', 1265)->orderBy('created_at', 'desc')->get();` should do the trick. – Jean-Philippe Murray Apr 07 '17 at 14:34

2 Answers2

3

You need to add the date value to the query:

select concat(to_user_id, from_user_id) as group_by,
       max(updated_at) as max_updated_at
from personal_messages pm
where (to_user_id = 1265) or (from_user_id = 1265)
group by group_by
order by max_updated_at desc

The date value could be created_at or updated_at, this depends on your actual requirement. You have to use an aggregate function like MAX though, because neither of these fields appears in the GROUP BY clause.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
1

Check out answer MySQL "Group By" and "Order By"

The problem is the the group by has requirements for order that are trumping order_by. There is not a 100% solution, but this link will give you something that should work for you.

Community
  • 1
  • 1
Danielle Cox
  • 88
  • 6