24

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)
Bob Benedict
  • 467
  • 1
  • 4
  • 10

5 Answers5

44

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)
opsb
  • 29,325
  • 19
  • 89
  • 99
  • Does this work with an array of strings? I've been trying to get this to work but for some reason it doesn't. In my console it works but in my model it doesnt.. ? – boydenhartog Jul 29 '16 at 06:02
5

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]})
Mischa
  • 42,876
  • 8
  • 99
  • 111
1

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.

seph
  • 6,066
  • 3
  • 21
  • 19
0

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)
Dana Mut
  • 1
0

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))
Dan Croak
  • 1,639
  • 13
  • 13
  • Worked perfectly, thanks. But is there a way to do it by combining the last 2 lines such that the database does the work? With an IN clause we can tell the database to take the output of one SELECT and use it for another SELECT via the IN. – Bob Benedict Jun 07 '11 at 03:47
  • Yup, using joins like mischa commented. – Dan Croak Jun 07 '11 at 04:01