How can i show only the results that have an association in rails 3?

Question

I am trying do a where statement that only brings up results that have that particular association.

For example:

A company has many statuses through company statuses. They can have multiple statuses that can be gold, silver, and/or bronze, or none at all. I am trying to have my results only return the companies that have a status (gold, silver, and/or bronze) and not the ones have no statuses.

Answer 1

Company.joins(:statuses).select("DISTINCT(companies.id), companies.*, statuses.*")

Answer 2

Your use case isn't very well articulated, but I think what you're wanting is to find Companies that have a status that matches a specified one:

Company.includes(:statuses).where('status.name = ?', params[:status_name])

That should give you the right query, assuming a Company has_many :statuses.

Answer 3

From the Ruby on Rails guide on Active Record Associations:

4.2.3 How To Know Whether There’s an Associated Object?

To know whether there’s and associated object just check association.nil?:

if @supplier.account.nil?

@msg = "No account found for this supplier"

end

http://guides.rubyonrails.org/association_basics.html#detailed-association-reference

Answer 4

If you have an associative table called companies_statuses:

to retrieve all companies with at least one status

Company.where("EXISTS (select 1 from companies_statuses where companies_statuses.company_id = companies.id)")

to retrieve all companies with no status

Company.where("NOT EXISTS (select 1 from companies_statuses where companies_statuses.company_id = companies.id)")