2

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?

Joel Grannas
  • 2,016
  • 2
  • 24
  • 46
  • maybe `Company.where(:name => 'Google').joins(:evaluations).minimum('evaluations.value')` ? not sure if works – fotanus May 03 '13 at 17:58
  • Your question begs further explanation, what do you mean when you say `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.` Do you want the single lowest score for each employee employed at the company, or do you want the 10 lowest employee evaluation score for a company, such that each employee evaluation score comes from a distinct employee? – Gui LeFlea May 03 '13 at 18:20
  • Your last sentence is what I want... I want to pull the 10 lowest distinct employee scores. – Joel Grannas May 06 '13 at 12:22

2 Answers2

3

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)
MrTheWalrus
  • 9,670
  • 2
  • 42
  • 66
0
@list = Evaluation.joins(:employee => :company)
  .where(:company => { :name =>  "Google" })
  .order(:score)
  .group(:employee => :id)
  .first(10)

IIRC, ordering by score should mean you don't have to bother with min functions and stuff as it will pick the first record when doing the grouping, which will be the lowest score. Give it a whirl.

Mike Campbell
  • 7,921
  • 2
  • 38
  • 51
  • I am using Postgres, and it throws an error on the .order column "evaluations.id" must appear in the GROUP BY clause or be used in an aggregate function – Joel Grannas May 06 '13 at 13:23