Order by associated model with pagination

Question

rails: '4.2.10' kaminari: '1.0.1'

I'm working with a very simple relationship where a Conversation has many ConversationMessages. I am displaying these Conversation records on an index, and am wanting to sort them by the ones which have the most recent ConversationMessage. Here's where things get interesting.

def index
    @conversations = current_user.conversations.includes(:conversation_messages).order("conversation_messages.created_at DESC").page(params[:page])
end

What's happening is when I order using conversation_messages.created_at, the majority of the records are not displaying in the view, in fact the pagination is gone completely; And yet if I append ?page=3 to the url you can still access additional pages; they all just have a very limited number of records on each one.

Also, if I remove .page(params[:page]) and get rid of pagination, all results display whether I order by the Conversation date or the ConversationMessage date.

It's just when ordering by ConversationMessage AND pagination are used simultaneously where everything seems to go haywire.

Any thoughts?

moveson · Accepted Answer · 2018-12-06T21:20:14.253

The query lends itself to a joins rather than an includes. You can do it, sort of, with a single query:

@conversations = current_user.conversations
    .select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
    .left_joins(:conversation_messages).group('conversations.id').order('recent_message_date desc')

This will produce the desired result, but it isn't reliable. For example, if you do:

>> @conversations.count

You'll get an error because ActiveRecord will replace the select clause with something you didn't intend. Calling size produces different SQL but also results in an error.

To do it right, you need to use a subquery. You can do this while staying in the Rails environment by using ActiveRecord's little-known #from method. This allows you to generate a subquery and then operate on that subquery, for example, by ordering it or filtering with a where clause.

>> subquery = current_user.conversations
     .select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
     .left_joins(:conversation_messages)
     .group('conversations.id')

The subquery is now an ActiveRecord association that contains the conversation objects you want, each having a tentative recent_message_date attribute. I say tentative because (as shown before) the attribute exists only if ActiveRecord doesn't decide to mess with our select clause.

To set things in stone, we have to give the subquery a name (you'll want to use the name of the table to avoid problems), and then grab everything from the subquery. Then we can count the resulting records, and we can also reliably sort by or filter from that attribute:

>> @conversations = Conversation.from(subquery, :conversations)
     .order('recent_message_date desc')

To make things tidy, we can create a method or two:

class Conversation < ApplicationRecord
  def self.with_recent_message_date
    select('conversations.*, max(conversation_messages.created_at) as recent_message_date')
         .left_joins(:conversation_messages)
         .group('conversations.id')
  end

  def self.most_recent_first
    order('recent_message_date desc nulls last')
  end
end

Or, if you prefer, you can write the methods as scopes on your class; the result is the same. Now you can write clear, expressive queries:

>> subquery = current_user.conversations.with_recent_message_date
>> @conversations = Conversation.from(subquery, :conversations).most_recent_first

In addition, you can add a filter or whatever else using the new attribute:

>> @conversations.where('recent_message_date > ?', Time.current - 3.days)

and you should be able to reliably paginate this result. I've tried a similar query using will_paginate and it works as expected.

Thank you for the detailed response, I will dig into this tomorrow :) — Tony Beninate, Dec 06 '18 at 21:05
Oh man! I can't thank you enough, this seems to be working! Never ever would have thought of this. One note, `.left_joins` does not seem to be a valid Rails method, but using `.joins` did the job. Thank you again! — Tony Beninate, Dec 07 '18 at 13:36
Glad it's working. Rails 5 has a method `#left_joins`. You're fine using `#joins` so long as you don't need to return conversations having no conversation_messages. If at some point you want to return message-less conversations, and you aren't able to upgrade to Rails 5, you can do it manually in Rails 4 using `.joins('left join conversation_messages on conversation_messages.conversation_id = conversations.id')`. — moveson, Dec 07 '18 at 16:44
Ahh makes sense, yea we're still on R4 at the moment. I'll note that for the future, thx again. — Tony Beninate, Dec 07 '18 at 18:47

score 0 · Answer 2 · answered Dec 06 '18 at 18:42

0

Basically you need to group your results conversation wise since there is a one to many relation from conversation to conversation_messages. I'd do something like this

@conversations = current_user.conversations
  .joins(:conversation_messages)
  .group("conversations.id, recent_message_date")
  .order("recent_message_date DESC")
  .select("conversations.*, conversation_messages.created_at as recent_message_date")
  .page(params[:page])

Hope this helps.

answered Dec 06 '18 at 18:42

bhanu

2,260
3
24
35

Thank you for response, but my SQL game is pretty weak and in trying to adapt this it didn't seem to work. Ran into several errors like `column "recent_message_date" does not exist`, but after ditching the select and just manually entering `conversation_message.created_at`, ran into some more. eventually got it running, but at that point i ended up with the same issue i had before :/ – Tony Beninate Dec 06 '18 at 19:45
Can you post your updated code and the error you are getting? @Tony Beninate – bhanu Dec 06 '18 at 22:23

Order by associated model with pagination

2 Answers2