Saturday, September 18, 2010

Arel and Rails 3 - The Rage and Grace

I've been spending my time recently rebuilding the front end of a logging system we have to use Rails 3. This has been a lot of fun and I've come up with some fun code (pagination and naturaldate). The new ActiveRecord::Relation class has really made my data interactions streamlined and readable -- including (and especially) performant.

Unfortunately, I've found that Arel and Rails 3's journey into SQL's GROUP BY clause was a little brief. While I appreciate ActiveSupport::OrderedHash as a combination of array and hash to effectively express the results of a group by -- and effectively any other such ordered hash data set -- I was frustrated and disappointed by the way it handles multiple properties in the GROUP BY or SELECT clause. Additionally, I was thrown off for some time before I realized the fact that :count() returns an OrderedHash and not a number when dealing with a :group type active relation. Perhaps it's my own inability to figure out how to use Rails' or Arel's (poorly (if at all) documented) methods, but I've found that if I want any information outside of a basic

SELECT COUNT(*), property_name FROM tbl GROUP BY property_name

that I am forced to do more than one query. Hmm, I thought ORM was supposed to make my life easier or at least not make my website slower?

In my specific case, I need to group by several properties and paginate over the results. On top of that, I also needed an aggregate result in my select list. Not to mention the pagination parts.

SELECT prop1, prop2, COUNT(prop2) FROM tbl GROUP BY prop1, prop2

This is how I solved it:

counts = Model.
select("prop1, prop2, COUNT(prop2) AS 'prop3').
group("prop1, prop2")

num_pages = counts.all.size

page = counts.
limit(page_size).
offset(page_num * page_size)


There are two important things to notice above as hacks/sadness
  1. counts.all.size -- This will query the full set from the database as opposed to COUNT(*)
  2. COUNT(prop2) AS 'prop3' -- Hmm, what? Since rails will ignore anything in the select list that isn't a property on the referenced model's table, I have to trick into loading my aggregation result into prop3. Ugly.
Part 2:

Now that all of that is behind me, I'm faced with a new problem! Rails 3 doesn't support :include with :group! Oh pain, why can't life be simple? How did I solve this? Well, more hackery.

include() works by creating an instance of the included class and then uses its :find method to load all the referenced records after the initial model's data has been loaded in what might be called lazy loading, but what seems more like 1 + 1 versus N + 1 loads. Rails is able to programmatically do this in an elegant fashion by loading a reflections property on every ActiveRecord that defines a set of all foreign key relations to the singleton of that foreign key's ActiveRecord (Model) class.

Using this knowledge, I can imitate that same behavior by collecting all the referenced foreign keys in my select class (into :ids) and do the following,

fk1_records = Model1.new.reflections[fk1_class_name.to_sym].klass.find(ids)

I can then iterate over the result set and define the relationships accordingly.

1 comment:

  1. UPDATE:
    Decided directly executing the SQL would be faster:

    arel = counts.arel.compiler
    pages = self.connection.execute(
    "SELECT COUNT(*) FROM (
    SELECT 1 FROM #{arel.from_clauses} WHERE #{arel.where_clauses} GROUP BY #{arel.group_clauses}
    ) tbl "
    ).fetch_row[0].to_i / PAGE_SIZE + 1

    ReplyDelete