CODEWITHSUNDEEP
ruby-on-rails
Rhythm Walia
Rhythm Walia

Reputation: 161

Querying on association table

I am stuck in a rails problem, being a noob here. Given the following relationships.

conversation has_many messages
messages belongs_to conversation
messages has_many documents and vice versa, through document_messages

I need to find all documents in that conversation for a given conversation id. I got it working with following

c.messages.map(&:documents).select{ |doc| doc.length > 0 }

But as I understand it takes everything from database and does filtering on the application, but I need to filter it on the database (where clause?) for e.g. conversation.messages.where(document.exists).map(&:documents)

Edit: I came up with following sql query which is working

SELECT document.id FROM
conversation INNER JOIN message
ON conversation.id = message.c_id
INNER JOIN document_messages
ON message.id = document_messages.m_id
INNER JOIN document
ON document.id = document_messages.d_id
where conversation.id = given_id

Upvotes: 1

Views: 183

Answers (3)

Rhythm Walia
Rhythm Walia

Reputation: 161

So, I finally managed to write the query.

Document.joins(document_messages: [{ message: :conversation }]).where(conversations: { id: conversation_id})

which can be simplified to

 Document.joins(messages: :conversation).where(conversations: { id: conversation_id})

Upvotes: 1

Hay.P
Hay.P

Reputation: 48

Not entirely confident with how this plays with a join table but this could be what you need?

https://apidock.com/rails/ActiveRecord/QueryMethods/includes

Something like:

messages = conversation.messages.includes(:documents)
documents = messages.documents

Upvotes: 0

Dimitrius Lachi
Dimitrius Lachi

Reputation: 1287

Use Through association: https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association

Conversation
  has_many :messages
  has_many :documents, through: :messages
end

Messages
  belongs_to :conversation
  has_many :documents
end

Using this, you can do the following:

  conversation = Conversation.first
  conversation.documents

And you have all documents of a conversation!

Upvotes: 1

Related Questions