Include, Select, Sort, Limit from multiple models (single query)

Question

I need to create a single query that includes data from the following tables:

*Conversation: a model that groups messages between users

class Conversation < ActiveRecord::Base
  # Associations
  has_many :messages, dependent: :destroy
  has_many :conversation_participants
  has_many :users, :through => :conversation_participants
  ## Attributes title, created_at, updated_at
end

* ConversationParticipant: a model that keeps track of the users of the conversation

class ConversationParticipant < ActiveRecord::Base
  ## Associations
  belongs_to :conversation
  belongs_to :user
  ## Attributes conversation_id, user_id, seen, created_at, updated_at
end

* Message: a model that keeps track content and sender

class Message < ActiveRecord::Base
  belongs_to :conversation
  belongs_to :sender, :class_name => "User"
  ## Attributes sender_id, content, conversation_id, created_at, updated_at
end

*User: a model with attribute name

How to get the following in a single query?

limit of (5) recent messages from Message of uniq Conversation

where user_id = current_user.id from ConversationParticipant

order seen = false, then updated_at DESC from ConversationParticipant

includes Conversation

includes (Message sender) => User

includes the other participant from ConversationParticipant => User

Note: includes and select are important, as this question is meant to reduce the number of queries.

Would it make sense for Message to have a ConversationParticipant instead of a User? Also, I'm unclear of what you mean by the Message of uniq Conversation under number 1. What are you currently using? Seeing your current queries might help me to understand what it is you are going for. Also seeing what you expect the returned objects to look like might be helpful too. Additionally, this might be a good candidate for a database view depending on what you want to do with this information and could greatly simplify the query... — user3334690, Nov 13 '14 at 15:43

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Here is how I included all the needed models, this query is translated to 5 sql queries since preload doesn't join (runs in a separate query).

Message.joins("LEFT JOIN messages AS m ON messages.id != m.id 
              AND m.conversation_id = messages.conversation_id 
              AND messages.created_at < m.created_at")
       .where('m.id IS NULL')
       .joins("INNER JOIN conversation_participants AS cp 
              ON cp.conversation_id = messages.conversation_id 
              AND cp.user_id = #{user_id}")
       .order("cp.seen, cp.updated_at DESC")
       .limit(5)
       .includes(:sender)
       .includes(conversation: [{conversation_participants: :user}])

Include, Select, Sort, Limit from multiple models (single query)

How to get the following in a single query?

1 Answers1

Here is how I included all the needed models, this query is translated to 5 sql queries since preload doesn't join (runs in a separate query).

Linked