Converting SQL into Arel or Squeel

Question

tl;dr

How to convert below SQL to Arel(or whatever is considered standard in Rails)

@toplist = ActiveRecord::Base.connection.execute(
     'select ci.crash_info_id,
        count(distinct(user_guid))[Occurences], 
        c.md5 
      from crashes ci 
        join crash_infos c on c.id=crash_info_id 
      group by ci.crash_info_id
      order by [Occurences] desc')

--- end of tl;dr ----

I'm working on a small web project, it's goal is to take our customers crash reports(when our desktop app crashes, we send diagnostics to our servers), analyze them and then display what is the most common bug that causes our application to crash.. So that we concentrate on fixing bugs that affect biggest chunk of our users..

I have 2 tables:

1. crash_infos - id, md5(md5 of a stacktrace. in .net stacktrace and exception messages are sometimes translated by .net to user's native language! so I basically sanitize exception, by removing language specific parts and produce md5 out of it)

2. crashes - id, user_guid(user id), crash_info_id(id to crash_infos table)

Now the question, I wanted to make a query that shows most common crashes for unique user(avoid counting many times same crash for same user) and sort it by number of crashes. Unfortunately I didn't know enough Arel(or whatever is the ruby on rails way), so I ended up with raw SQL :(

@toplist = ActiveRecord::Base.connection.execute(
  'select ci.crash_info_id,
     count(distinct(user_guid))[Occurences], 
     c.md5 
   from crashes ci 
   join crash_infos c on c.id=crash_info_id 
   group by ci.crash_info_id 
   order by [Occurences] desc')

How can I convert this, to something more "railsy" ?

Thank you in advance

Answer 1

Not sure if this is actually any better but at least you should have a database independent query...

crashes     = Arel::Table.new(:crashes)
crash_infos = Arel::Table.new(:crash_infos)

crashes.
    project(crashes[:crash_info_id], crash_infos[:md5], Arel::Nodes::Count.new([crashes[:user_guid]], true, "occurences")).
    join(crash_infos).on(crashes[:crash_info_id].eq(crash_infos[:id])).
    group(crashes[:crash_info_id]).
    order("occurences DESC").
    to_sql

Gives:

SELECT "crashes"."crash_info_id", "crash_infos"."md5", COUNT(DISTINCT "crashes"."user_guid") AS occurences FROM "crashes" INNER JOIN "crash_infos" ON "crashes"."crash_info_id" = "crash_infos"."id" GROUP BY "crashes"."crash_info_id" ORDER BY occurences DESC