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.

4 comments:

  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
  2. REALLY VERY EXCELLENT INFORMATION. I AM VERY GLAD TO SEE YOUR BLOG FOR THIS INFORMATION. THANKS FOR SHARING. KEEP UPDATING.

    NO.1 SYSTEM INTEGRATION SERVICES | SYSTEM INTEGRATION MIDDLEWARE | MASSIL TECHNOLOGIES

    ReplyDelete
  3. REALLY VERY EXCELLENT INFORMATION. I AM VERY GLAD TO SEE YOUR BLOG FOR THIS INFORMATION. THANKS FOR SHARING. KEEP UPDATING.

    Digital Marketing Course in Chennai
    Digital Marketing Training in Chennai
    Online Digital Marketing Training
    SEO Training in Chennai

    ReplyDelete