Ruby on Rails activerecord find average in one sql and Will_Paginate

Question

Hi all

I have the following model association: a student model and has_many scores.

I need to make a list showing their names and average, min, max scores. So far I am using student.scores.average(:score) on each student, and I realise that it is doing one sql per student. How can I make the list with one joined sql?

Also how would I use that with Will_Paginate plugin?

Thank you

Answer 1

You want the :group and :select options to Student.find. This should work for you:

students = Student.all(
  :select => "
    students.*,
    AVG(scores.score) as avg_score,
    MIN(scores.score) as min_score,
    MAX(scores.score) as max_score",
  :joins => :scores
  :group => 'students.id')

The calculated columns are available just like the real columns though they obviously won't be saved

students.first.avg_score
students.first.min_score
students.first.max_score

For using WillPaginate, just include your :page, :per_page, ... options and call Student.paginate instead of find. If it turns out that the pagination is getting the wrong number of pages because of the :group option, just add this: :total_entries => Student.count to your arguments