ARel: Nifty & Vexingly Undocumented
By Philip Champon Developer Team Lead
tl;dr Reference (examples and output, sans commentary)
Ambiguous column reference, what ever do you mean?
Ryan Bates was good enough to sneak in some hints on better ActiveRecord encapsulation in his excellent “Advanced Queries in Rails 3”. I began using #merge and #& immediately. Unfortunately, not all of our code was ready to live in an encapsulated world. Quite often, I found myself with SQL errors, because columns were referenced as symbols and ActiveRecord failed to qualify the name space… ActiveRecord only converts symbols in finder methods (find* and #where) to canonically named columns. Any #group, #having, #select, or #order symbols will burn you sooner or later, particularly if they’re part of a scope.
I began solving this on my own, by using the #arel_table class method within my model scopes. As far as piecemeal solutions went, this kept me happy… But I wanted to share the joy with my co-workers and I couldn’t justifiably ask them to read ARel code and tests (since ARel mostly lacks anything nearing useful documentation). I was forced to finally make a presentation…
Ambiguous column references in scopes can be merged into other queries.
The following snippet is a valid scope. It exemplifies the shortsighted tendencies a lot of Rails models assume. Once you attempt to merge this with another model, you’ll find your SQL server returning an ambiguous column reference.
class Product < ActiveRecord::Base scope :with_id, select(:id) end => SELECT id FROM "<T>"
class Category < ActiveRecord::Base scope :with_product, joins(:products).merge Product.with_id end => SELECT id FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id"
Better encapsulation yields more reusable and saner code. This can be rectified by hardcoding the table name, using the #table_name class method, or accessing the symbol via the #arel_table class method.
I’m partial to the arel_table method, because it gives you an Arel::Table object, which is very powerful. Aside from canonically naming the field for you, ARel will also correctly quote the name for each database engine you use.
select("<T>.id")
=> SELECT <T>.id FROM "<T>"
select("#{table_name}.id")
=> SELECT <T>.id FROM "<T>"
select(arel_table[:id]) => SELECT "<T>"."id" FROM "<T>"
Ambiguous and ugly
def self.cheaper_than(price)
where("price < ?", price)
end
=> SELECT * FROM "<T>" WHERE price < N
This method is ambiguous and it can be more easily written using ARel’s relational algebra. ARel supports many predicates, including eq, not_eq, in, not_in, matches (LIKE or ILIKE in PgSQL), does_not_match (NOT LIKE or NOT ILIKE in PgSQL), gt, gteq, lt, and lteq. Each of the aforementioned predicates also supports _any and _all suffixes, adding AND and OR support for arrays.
def self.cheaper_than(price) where(arel_table[:price].lt(price)) end => SELECT * FROM "<T>" WHERE "<T>"."price" < N
scope :order_id_asc, order(arel_table[:id].asc) => ORDER BY "<T>"."id" ASC
scope :order_id_desc, order(arel_table[:id].desc) => ORDER BY "<T>"."id" DESC
scope :order_name_ci, order(arel_table[:name].lower)
=> ORDER BY LOWER("<T>"."name")
When using aggregate functions the results, in the SELECT, is aliased as function_id. This is true for all of the aggregates except count, which will remain unaliased, by default.
scope :avg_cost, arel_table[:cost].average => SELECT AVG("<T>"."cost") as avg_id FROM "<T>" scope :avg_cost, arel_table[:cost].average.as(:avg) => SELECT AVG("<T>"."cost") as avg FROM "<T>"
scope :count_cost, arel_table[:cost].count => SELECT COUNT("<T>"."cost") FROM "<T>" scope :count_cost, arel_table[:cost].count.as(:count) => SELECT COUNT("<T>"."cost") AS count FROM "<T>"
Also, when using multiplication or division infix operators you can alias the results, but you cannot alias addition or subtraction results. Furthermore, there are no infix operators for SQL bitwise operations.
scope :add_n1_n2, arel_table[:n1] + arel_table[:n2] scope :mul_n1_n2, arel_table[:n1] * arel_table[:n2].as "foo"
scope :order_id_asc, arel_table.grouping(arel_table[:id].gt(1).and(arel_table[:n1].gt(1))
=> SELECT * FROM "<T>" WHERE ("<T>"."id" > 1 AND "<T>"."n1" > 1)
