Display latest messages from messages table, group by user

Question

I'm trying to create an inbox for messaging between users.
Here are the following tables:

Messsages
Id | Message_from | message_to | message
1  | 2            |   1        | Hi
2  | 2            |   1        | How are you
3  | 1            |   3        | Hola
4  | 4            |   1        | Whats up
5  | 1            |   4        | Just Chilling
6  | 5            |   1        | Bonjour

Users
Id | Name
1  | Paul
2  | John
3  | Tim
4  | Rob
5  | Sarah
6  | Jeff

I'd like to display an inbox showing the list of users that the person has communicated and the last_message from either users

Paul's Inbox:

Name | user_id | last_message
Sarah| 5       | bonjour
Rob  | 4       | Just Chilling
Tim  | 3       | Hola
John | 2       | How are you

How do I do this with Active Records?

Mohamad · Answer 1 · 2014-05-19T01:34:15.927

0

How about this:

@received_messages = current_user.messages_to.order(created_at: :desc).uniq

If you want to include messages from the user as well, you might have to do a union query, or two queries, then merge and join them. I'm just guessing with some pseudocode, here, but this should set you on your way.

received_messages = current_user.messages_to
sent_messages = current_user.messages_from
(received_messages + sent_messages).sort_by { |message| message[:created_at] }.reverse

This type of logic is belongs to a model, not the controller, so perhaps you can add this to the message model.

edited May 19 '14 at 01:34

answered May 19 '14 at 01:07

Mohamad

34,731
32
140
219

@user3649729 ahhh.. that's a bit more tricky... let me think about it for bit! – Mohamad May 19 '14 at 01:27
@user3649729 since you are using the same table, you might be able to do some type of union query, or two queries, then merge and sort the results... – Mohamad May 19 '14 at 01:28
The issue there is that once I join the two queries. I cannot sort the result. Thanks again! – user3649729 May 19 '14 at 01:29
@user3649729 I took out the code. It was wrong... See my earlier two comments. – Mohamad May 19 '14 at 01:29
So you could please advise on how I can "do some type of union query, or two queries, then merge and sort the results." thanks. – user3649729 May 19 '14 at 01:30

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

This should be rather efficient:

SELECT u.name, sub.*
FROM  (
   SELECT DISTINCT ON (1)
          m.message_from AS user_id
        , m.message AS last_message
   FROM   users    u
   JOIN   messages m ON m.message_to = u.id
   WHERE  u.name = 'Paul'   -- must be unique
   ORDER  BY 1, m.id DESC
   ) sub
JOIN  users u ON sub.user_id = u.id;

Compute all users with the latest message in the subquery sub using DISTINCT ON. Then join to table users a second time to resolve the name.

Details for DISTINCT ON:
Select first row in each GROUP BY group?

Aside: Using "id" and "name" as column names is not a very helpful naming convention.

edited May 23 '17 at 11:50

Community

1
1

answered May 19 '14 at 01:50

Erwin Brandstetter

605,456
145
1,078
1,228

Great! Thanks Erwin. How do I do this in Active Records please – user3649729 May 19 '14 at 01:55
@user3649729: You could use raw SQL like explained by mu in this related answer: http://stackoverflow.com/questions/10633412/table-join-sql-to-rails-active-record-query/10639918#10639918 – Erwin Brandstetter May 19 '14 at 02:04

score 0 · Answer 3 · answered May 19 '14 at 03:19

0

scope :ids_of_latest_per_user, -> { pluck('MAX(id)').group(:user_id) }
scope :latest_per_user,        -> { where(:id => Message.latest_by_user) }

Message.latest_per_user

answered May 19 '14 at 03:19

spickermann

100,941
9
101
131

Display latest messages from messages table, group by user

3 Answers3

Linked