CODEWITHSUNDEEP
sqlruby-on-railsrubyassociations
RomanOks
RomanOks

Reputation: 722

Query for where method through three associations

I have three models:

Invitation.rb

belongs_to :department

Department.rb

belongs_to :organisation
has_many :invitations

Organisation.rb

has_many :departments

Organization table has tag field.

I need to write a query to search for Invitations, for the organization of which there is a tag field with the content 'First tag'

Something like this:

Invitation.joins(:department).joins(:organisation).where(tag: 'First tag')

But I don't know how to build such a query.

Upvotes: 1

Views: 43

Answers (2)

sensadrome
sensadrome

Reputation: 482

To do what you are trying to do without changing your models you should pass a hash to the joins method like so:

Invitation.joins(department: :organisation).where(organisations: { tag: 'First tag' })

This tells ActiveRecord to use the department association as defined on the Invitation model and the organisation association from the Department model

To make your life a bit easier you can add a has_many through association like this:

class Invitation < ApplicationRecord
  belongs_to :department
  has_one :organisation, through: :department
end

class Department < ApplicationRecord
  belongs_to :organisation
  has_many :invitations, inverse_of: :department
end

class Organisation
  has_many :departments, inverse_of: :organisation
  has_many :invitations, through: :departments
end

Now you can use the invitations relation on Organisation as follows:

Invitation.joins(:organisation).where(tag: 'First tag')

To see the sql (e.g. in the console) you can use the #to_sql method:

Invitation.joins(:organisation).where(tag: 'First tag').to_sql

Finally, if you use a scope (and dont like the hash in the where method in which you have to specify the table) you can use a "scope" and merge as follows:

class Organisation
  class << self
    def just_tagged
      where(tag: 'First tag')
    end
  end
end

Now you can refer to the scope within your query using merge:

Invitation.joins(:organistion).merge(Organisation.just_tagged)

Upvotes: 1

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33430

Invitation.joins(department: :organisation).where(organisations: { tag: 'First tag' })

Why? Because to "reach" the organisations table from invitations you can use the relationship between the departments table.

Table.joins(...).joins(...)

Is "joining" twice the same table, what you need is to reference the relatinoship between departments and organisations. That you can do it as:

{ department: :organisation }

Passing that to join loads a JOIN clause in the receiver with departments and in departments with organisations.

Notice the where clause can be used as { organisations: { tag: ... } } or ["organisations.tag = ?", ...], they can be used interchangeably. What you use is just a matter of preference yours or from your colleagues.

Upvotes: 2

Related Questions