CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5
user12763413
user12763413

Reputation: 1347

Double Join in rails query

I have these three tables as below

office.rb

has_many :documents
has_one :information

information.rb

belongs_to :office

document.rb

belongs_to :office

I am trying to write a query where as below

documents_controller.rb

def search
 @documents = Document.all
 @documents.joins(:office).where("offices.name ILIKE ?", "%#{params[:search]}%") OR @documents.joins(:office).joins(:information).where("informations.first_name ILIKE ? OR informations.last_name ILIKE ?", "%#{params[:search]}%", "%#{params[:search]}%")
end

I am trying to achieve the above statement but I am doing something wrong. Please help me fix this query

Upvotes: 0

Views: 47

Answers (1)

Aguardientico
Aguardientico

Reputation: 7779

So, the idea is to retrieve any document where the office's name is the search term or where the information first/last name is the search term, right?

The first step is to create the joins:

Document.joins(office: :information)

The second step is to create the condition:

where("offices.name ILIKE :term OR informations.first_name ILIKE :term OR informations.last_name ILIKE :term", term: "%#{params[:search]}%")

and joining both sentences:

Document.joins(office: :information).where("offices.name ILIKE :term OR informations.first_name ILIKE :term OR informations.last_name ILIKE :term", term: "%#{params[:search]}%")

there are other fancy ways to do the same thing like using or scope but probably will be more complex to understand:

search_term = "%#{params[:search]}%"
base_query = Document.joins(office: :information)
office_scope = base_query.where("offices.name ILIKE :term", search_term)
first_name_scope = base_query.where("informations.first_name ILIKE :term", search_term)
last_name_scope = base_query.where("informations.last_name ILIKE :term", search_term)
office_scope.or(first_name_scope).or(last_name_scope)

Upvotes: 2

Related Questions