How can I apply LIMIT/ORDER conditions to an ActiveRecord Join?

Question

Currently I have a Conversation has many Messages relationship, and a User has many Conversations relationship.

I would like to create an ActiveRecord Query to get The Last Message of each conversation that a user has.

Let's say I have the conversations ids in an array...

ids = [24, 22, 23]

This query:

Message.where(conversation_id: ids).joins(:conversation).order(created_at: :desc)

... is correct in terms that it returns ALL the Messages across all the user's conversations.

Using the same query above, If I map an array of the conversation_ids:

Message.where(conversation_id: ids).joins(:conversation).order(created_at: :desc).map(&:conversation_id)

I get an array like this: [24, 24, 22, 22, 23, 22] that tells me there are 3 messages in conversation with conversation_id=22, 2 messages with conversation_id=24, 1 with conversation_id=23.

This is good, But now my Question now is, How can I create an ActiveRecord Query to get just One Message from each Conversation? (the last one that was created)

I assume I have to use the limit()/order() methods, but I have no idea how to do it, it's a little too advanced for me.

Thanks for all your help in advance.

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

joins can accept a string, and you can specify any join you want as plain text. See doco.

Example:

User.joins("LEFT JOIN bookmarks ON bookmarks.bookmarkable_type = 'Post' AND bookmarks.user_id = users.id")
# SELECT "users".* FROM "users" LEFT JOIN bookmarks ON bookmarks.bookmarkable_type = 'Post' AND bookmarks.user_id = users.id

As for the problem of joining to the latest record, that's another question in it's own right, and has an answer on stackoverflow here.

Example:

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

edited May 23 '17 at 10:33

Community

1
1

answered Aug 11 '16 at 23:02

joshua.paling

13,762
4
45
60

Quick question, does this solution apply if I want to retrieve the N-Last records? – jlstr Aug 11 '16 at 23:07
To be honest, of the top of my head I'm not sure how you'd do that – joshua.paling Aug 11 '16 at 23:14
Thanks for you answer. It appears there isn't an easy solution to this problem available, at least not one that doesn't rely on having to write/embed SQL into Ruby, quite unfortunate. – jlstr Aug 11 '16 at 23:17
You could just denormalise your database, and store the 'last_message_id' field on each row of your conversations table. It's not best practices strictly speaking, but it's sometimes the most practical thing to do. – joshua.paling Aug 11 '16 at 23:31
I agree, but I will need the N-last, not just the last. Otherwise, I agree that option would be perfect. – jlstr Aug 11 '16 at 23:51
Since there's no RoR answer to this question, I will accept yours. Thank you very much for your kind help! – jlstr Aug 23 '16 at 21:05

How can I apply LIMIT/ORDER conditions to an ActiveRecord Join?

1 Answers1