Advanced activerecord query where association with a limit

Question

I'm trying to do a more advanced query with activerecord and can't figure out the best way to write it. Hopefully this makes sense...

My POSTGRES/rails app has models:

Company has_many :employees
Employee has_many :evaluations

Evaluations has decimal column :score. I want to get the 10 lowest evaluation scores for a company by name. BUT I also only want to pull a SINGLE lowest score from each employee. I don't want to have 5 of the 10 scores in my results list all coming from the same employee ID.

I have:

@list = Company.where(:name => 'Google').joins(:evaluations)

But I am struggling with how to limit the evaluations to a single low value on a single employee.id

What is the best way to do this?

Answer 1

Here's the 'off-the-top-of-my-head' guess:

Employee.
  select('employees.*, min(evaluations.score) as lowest_score').
  joins(:company, :evaluations).
  where(:company => {:name => 'Google'}).
  group('employees.id').
  order('lowest_score').
  limit(10)