0

I have the following models:

Company has_many Employees has_many Projects

Projects have a deadline:dateTime

Now I want, with a statement that generates a single SQL, to get the next deadline from any project of a given company.

i.e. 

def get_next_deadline
   * * * select deadline from projects where employee_id in (employees.each |i| )) * * *
end

What I don't want is to iterate over all the companies Employees and check for the next deadline, accessing the DB once for each Employee.

Is there a more elegant find command than 

find_by_sql?
EasierSaidThanDone
  • 1,877
  • 4
  • 20
  • 29

1 Answers1

1

if you add

has_many :projects, :through => :employees

to your company model, then you can do

some_company.projects.order('deadline').first

to find the project with the first deadline for that company

Frederick Cheung
  • 83,189
  • 8
  • 152
  • 174
  • Nice, much more elegant! But why does it need to do an inner join between employees and projects. Wouldn't it be smarter to generate a 'WHERE...IN' query like I outlined in my question? – EasierSaidThanDone Jul 01 '12 at 17:57
  • subqueries are generally slower than inner joins, as would be a first query to get a list of ids followed by a second query with an IN clause (especially if the list of employees was long). But if in doubt, benchmark! – Frederick Cheung Jul 01 '12 at 19:48