"Rails way" to execute a seml-complex query

Question

I have a semi-complex query in a Rails 4 project that's using postgres. I'd like to see if it's possible to translate the query to ActiveRecord / Arel and if the Rails convention is to use ActiveRecord::Base.connection.execute(sql) if it's necessary to execute raw SQL.

Here's a query I want to execute:

select
  sum(case when is_graded = true then 1 else 0 end) graded,
  sum(case when is_canceled = true then 1 else 0 end) canceled,
  sum(case when is_graded IS NULL and is_canceled IS NULL then 1 else 0 end) in_progress
from exams
where user_id = 1 and quiz_id = 114;

I want the result in the format:

{"graded"=>"2", "canceled"=>"2", "in_progress"=>"1"}

This gets me the answer I'm looking for, but it seems ugly and I want to see if there's a better way:

sql="select
  sum(case when is_graded = true then 1 else 0 end) graded,
  sum(case when is_canceled = true then 1 else 0 end) canceled,
  sum(case when is_graded IS NULL and is_canceled IS NULL then 1 else 0 end) in_progress
from exams
where user_id = 1 and quiz_id = 114;"

result = ActiveRecord::Base.connection.execute(sql)

result.to_a.first

Reiteration of my questions:

1. Is it possible to write this query using Arel / ActiveRecord? If so, how?

2. Is it "Rails convention" to use ActiveRecord::Base.connection.execute(sql) if you need to execute raw SQL in Rails? This answer says it's best to run the SQL directly when it's complex and this answer is where I found ActiveRecord::Base.connection.execute(sql).

Answer 1

Probably the easiest way to go through ActiveRecord would be something like this:

Exam.where(:user_id => 1, :quiz_id => 114).select(%q{
  sum(case when is_graded then 1 else 0 end) graded,
  sum(case when is_canceled then 1 else 0 end) canceled,
  sum(case when is_graded IS NULL and is_canceled IS NULL then 1 else 0 end) in_progress
})[0]

That will give you an Exam instance in result but this won't be a normal Exam with id, is_graded, ... attributes, this one will have graded, canceled, and in_progress attributes. You see, when you say select('...'), AR will produce objects whose attributes match the named columns in the SELECT clause.

Then a call to the attributes method should give you your Hash:

result = Exam.where(:user_id => 1, :quiz_id => 114).select(%q{...})[0].attributes
# result is now like {"graded"=>"2", "canceled"=>"2", "in_progress"=>"1"}

You could probably express those SUM and CASE expressions with AREL methods but it would be a big ugly mess so I wouldn't bother.