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').
num_pages = counts.all.size
page = counts.
offset(page_num * page_size)
There are two important things to notice above as hacks/sadness
- counts.all.size -- This will query the full set from the database as opposed to COUNT(*)
- 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.
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.