Subquery in Rails report generation

Question

I'm building a report in a Ruby on Rails application and I'm struggling to understand how to use a subquery.

Each 'Survey' has_many 'SurveyResponses' and it is simple enough to retrieve these however I need to group them according to one of the fields, 'jobcode', as I only want to report the information relating to a single jobcode in one line in the report.

However I also need to know the constituent data that makes up the totals for that jobcode. The reason for this is that I need to calculate data such as medians and standard deviations and so need to know the values that make the total.

My thinking is that I retrieve the distinct jobcodes that were reported on for the survey and then as I loop through these I retrieve the individual responses for each jobcode.

Is this the correct way to do this or should I follow a different method?

Answer 1

You could use a named scope to simplify getting the groups of responses:

named_scope :job_group, lambda{|job_code| {:conditions => ["job_code = ?", job_code]}}

Put that in your response model, aand use it like this:

job.responses.job_group('some job code')

and you'll get an array of responses. If you're looking to get the mean of the values of one of the attributes on the responses, you can use map:

r = job.responses.job_group('some job code')
r.map(&:total)
=> [1, 5, 3, 8]

Alternatively, you might find it quicker to write custom SQL in order to get the mean / average / sum of groups of attributes. Going through rails for this sort of work may cause significant lag.

ActiveRecord::Base.connection.execute("Custom SQL here")

Answer 2

You can also use Model.find_by_sql()

For example:

class User < Activerecord::Base
  # Your usual AR model
end

...

def index
  @users = User.find_by_sql "select * from users"
  # etc
end