CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3where-clause
Bob Benedict
Bob Benedict

Reputation: 467

How do you do a Rails 3 select IN using where clauses

I am using Rails 3 and I need to do a select where the primary key of the records is IN a resulting previous select. You can do this easily using straight SQL using an IN. Here is the obviously incorrect way I have done what I need. What's the Rails way to do this well:

@task = Link.find(params[:id])
clients = Client.where('task_id = ?',@task.id).select('DISTINCT(company_id)')
company_list = []
clients.each do |client|
  company_ids << client.company_id
end
@companies = Company.where(:id => company_ids)

Upvotes: 24

Views: 21217

Answers (5)

Dana Mut
Dana Mut

Reputation: 1

You can simply use find, as find by id accepts a specific id (1), a list of ids (1, 5, 6), or an array of ids ([5, 6, 10]), see: http://apidock.com/rails/ActiveRecord/Base/find/class

@companies = Company.find(company_ids)

Upvotes: 0

opsb
opsb

Reputation: 30211

As others have mentioned I'd use join in this case. The syntax for using "in" is also very simple though e.g.

company_ids = [1,2,3,4]
@companies = Company.where("id in (?)", company_ids)

Update

Actually it's even simpler than that now (I think rails 3+), you can do

company_ids = [1,2,3,4]
@companies = Company.where(id: company_ids)

Upvotes: 44

Mischa
Mischa

Reputation: 43298

This does not answer your question about "select IN using where clauses", but I think your whole script can be rewritten in one line using joins. This should give the same result as your snippet above:

@companies = Company.joins(:clients).where(:clients => {:task_id => params[:id]})

Upvotes: 5

seph
seph

Reputation: 6076

I believe this will do what you are asking for:

@task      = Link.find(params[:id])
@companies = Company.where(:id => Client.where(:task_id => @task.id).select('distinct company_id').map(&:company_id))

You can view the sql by tacking .to_sql on the end in the console.

The join syntax in mischa's answer is probably more readable though.

Upvotes: 1

Dan Croak
Dan Croak

Reputation: 1669

I think the issue might be that you have company_list and company_ids. I would expect company_ids in the iterator to return something like:

NameError: undefined local variable or method `company_ids'

I think I might write this like:

@task      = Link.find(params[:id])
clients    = Client.where(:task_id => @task.id).select('distinct company_id')
@companies = Company.where(:id => clients.map(&:company_id))

Upvotes: 0

Related Questions