Ontic Oren

I’ve been working on some stats tracking.  This means a lot of complex queries, and sometimes ActiveRecord just doesn’t cut it.  Doing it the ruby way (or at least, the ruby way I know how to do) would often mean queries > 30 seconds.  A simple SQL query could get me the same data in <100ms.  For some reason, I was having a really hard time figuring out how to do direct SQL queries though. After banging my head, here’s what I figured out.

There are two ways to directly query the DB with AR: find_by_sql and ActiveRecord::Base.connection.execute. They each have their place, and I use both.

find_by_sql
find_by_sql is super easy, as it actually returns an ActiveRecord object. This means you don’t need to do anything unusual. It works on an object, and works great when you’re selecting fields from that object specifically. Here’s an example of how I use find_by_sql and some fun SQL to get a funnel of our users:

@funnel = User.find_by_sql("SELECT DATE_TRUNC('month', created_at) AS month,
             COUNT(id) AS registered,
             SUM(CASE WHEN verified_at IS NOT NULL THEN 1 ELSE 0 END) AS verified,
             SUM(CASE WHEN confirmed_billing_at IS NOT NULL THEN 1 ELSE 0 END) as confirmed
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month")

connection.execute
The other way to execute SQL is with connection.execute. This returns a database specific object, for example a PGrecord for my postgres database. You can then iterate over that object like any standard ruby array of hashes. The hash key will be the SQL header. Here’s an example where I calculate the # of new user signups per week:

sql = "SELECT COUNT(*), DATE_TRUNC('week', created_at) FROM USERS GROUP BY DATE_TRUNC('week', created_at)"
results = ActiveRecord::Base.connection.execute(sql)
results.each do |foo|
  puts "#{foo['date_trunc']} #{foo['count']}"
end

A nasty bug on ZED9 I’ve been avoiding the past few days was solved with a stupid single line.

In Rails, when you have a has_many relationship, it’s great and easy to be able to iterate through all the items. Assume you have models as such

class Foo < ActiveRecord::Base
    has_many :bars
end

class Bar < ActiveRecord::Base
    belongs_to :foo
end

So you go ahead and iterate through them:

@foo = Foo.find(:last)
@foo.bars.each {|f| f.your.logic.here}

If for anyreason, you’re counting on the ordering of those items, watch out! I was iterating through GPS coordinates, comparing them with the first one. SQL databases make no promise on ordering if you don’t ask for it, and every now and then I was getting totally insane results. Turns out the DB was feeding me the GPS coordinates in reverse order sometimes.

Luckily, rails makes this very easy. Just specify your ordering in the model:

class Foo < ActiveRecord::Base
    has_many :bars, :order => 'time ASC'
end

With that one line, I fixed half a dozen outstanding bugs. Mostly posting so I’m not stupid in the future.