CODEWITHSUNDEEP
sqlruby-on-railspostgresqlruby-on-rails-3.2
nzifnab
nzifnab

Reputation: 16092

Getting all results from `includes` while filtering the parent model based on included elements?

I'm working on a small bug in our app which I'll attempt to simplify here.

I have this company model (the actual model has many more associations, but this here displays the problem):

class Company < ActiveRecord::Base
  has_many :contacts

  def self.search(query, page=1)
    companies = includes(:contacts).order("companies.name ASC")

    if query.present?
      companies = companies.where(%(
        companies.name ILIKE :q,
        OR contacts.name ILIKE :q
      ), q: "%#{query}%")
    end

    companies.paginate(page: page)
  end
end

Now this mostly is working great. The only problem is when a company has 2 or more contacts, and the user searches based on one of the contact names. The company is found correctly, and our auto-complete UI displays the company. HOWEVER, when the user selects the company from the auto-complete they are supposed to be able to select a contact from a dropdown list, but the dropdown ends up only containing the one contact that they had filtered down to. The company should be filtered based on their search criteria, but that dropdown should still have all of the contacts for the company for the user to choose from.

I know why it's doing this, it's because rails is doing it's auto-load of all of the associations the moment that query runs so that it can both get all association properties and include all conditions at the same time.

It's running a select {every_attribute_from_every_included_table} left outer join {all_included_tables} type of query, but what I WANT it to do is this:

select companies.* from companies
left outer join contacts <and other included tables> on <join criteria>
where <filter criteria>

To filter the companies followed by a:

select contacts.* from contacts
where company_id IN (<company_ids from previous query>)

Which is how it would include entries normally if the include tables were not also involved in the where clause.

Rails is doing too much magic here and I want it to go back to it's normal magic instead of it's special magic! How can I do that? Or what's a better way to accomplish what I'm trying to accomplish (still filter based on contact name, but don't stop those other contacts from being included when I call company.contacts).

Upvotes: 0

Views: 118

Answers (2)

deefour
deefour

Reputation: 35360

Okay, let's do the query as you want

select companies.* from companies
left outer join contacts <and other included tables> on <join criteria>
where <filter criteria>

Let's manually override the .joins(...)

Company.includes(:contacts).
        joins("left outer join contacts c on c.company_id = companies.id").
        where(...)

This will leave the :contacts relation untouched, joining instead on a separate 'alias' of the same contacts table.

Upvotes: 1

Martin M
Martin M

Reputation: 8638

I see that you need two queries:

  • one to select the company (or companies)
  • one to select the contacts of a (selected) company

Using Rails magic you could select companies by using joins:

@companies = Company.joins(:contacts).where(...).uniq

giving SELECT DISTINCT "companies".* FROM "companies" INNER JOIN "contacts" ON ... WHERE (...)

and then get all the contacts of a specific company:

company= @companies.first
@contacts = company.contacts

Upvotes: 0

Related Questions