1

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.

NineBlindEyes
  • 579
  • 8
  • 18

4 Answers4

0

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)")
Gus
  • 942
  • 9
  • 32
0

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.

coreyward
  • 77,547
  • 20
  • 137
  • 166
  • Sorry for not explaining more. I'll edit the question to fix it. What i am trying to do though is retrieve only companies that don't have any statuses. They could have gold, silver, and/or bronze, or they could not have any. I am trying to find any company that has at least one status. – NineBlindEyes Aug 14 '11 at 20:07
  • I don't know if you can combine a join with an `is not null`. :/ – coreyward Aug 14 '11 at 20:10
  • Yeah, that's what makes it tough. I'm not sure how you'd do that. I feel like there has to be a way though. It can't be that uncommon. – NineBlindEyes Aug 14 '11 at 20:12
  • Try `Company.includes(:statuses).where('status.somecolumn IS NULL')` and if that doesn't work, share the query it creates (in your log) and we can maybe go from there? – coreyward Aug 14 '11 at 20:21
  • 1
    Well i think i figured it out. I can't really post the results though since i am not using that exact senario. I simplified it for the question. For my real code i have users that have companies that have dealer statuses. But i believe this works either way. User.find(:all, :include => [:statuses], :conditions => ["statuses.id IS NOT NULL"]) – NineBlindEyes Aug 14 '11 at 20:27
0

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

Svilen
  • 2,608
  • 24
  • 26
0
Company.joins(:statuses).select("DISTINCT(companies.id), companies.*, statuses.*")
fl00r
  • 82,987
  • 33
  • 217
  • 237