Arel Union does not support #order or #limit

rubyonrails-core
1

I just posted this ticket in Lighthouse, but thought perhaps some discussion here would help point me in the right direction toward a solution. Here is a description of the problem (from the ticket - https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/6693-arel-union-does-not-support-order-or-limit):

If we take two ActiveRecord::Relations and join them via union:

rel_a = ActiveRecord::Relation.new(MyModel).where(:id => 3)

rel_b = ActiveRecord::Relation.new(MyModel).where(:id => 5)

new_rel = rel_a.union(rel_b)

new_rel.to_s #=> “#Arel::Nodes::Union:0x3da6260”

new_rel.to_sql #=> “( SELECT my_models.* FROM my_models WHERE (my_models.id = 3) UNION SELECT my_models.* FROM my_models WHERE (top_items.id = 5) )”

Ideally, we’d be able to sort and limit the resulting UNION results in the SQL. According to the MySQL docs for the UNION operator, this should be possible (at least in MySQL). We’d call it like this:

new_rel.order(:created_at).limit(5)

new_rel.to_sql #=> “( ( SELECT my_models.* FROM my_models WHERE (my_models.id = 3) ) UNION ( SELECT my_models.* FROM my_models WHERE (top_items.id = 5) ) ORDER BY created_at ASC LIMIT 5 )”

Currently, two things prevent this from being possible:

  1. The Arel::Nodes:Union node inherits from Arel::Nodes::Binary, with no additional methods, and as such, does not support order or limit methods.
  1. The Arel::Nodes::Union.to_sql method should be wrapping both the :left and the :right Relation SQL strings in parentheses, so that the order and limit clauses may work on the UNION itself, according to the MySQL docs.

I can work on a patch for this with tests, but I’m still digging into Arel and figuring everything out. Is there a better approach than adding the order and limit methods to the Arel::Nodes::Union class? Would this even work correctly in the AST tree, with Union being a Node?

I thought I saw talk somewhere of making the union method return a new Relation object rather than a Union object, which makes more sense to me, since it’s really just an alternative method of joining two Relations. I.e. merging two relations with & gives you a Relation resulting in their intersection, couldn’t we use this to merge two relations with | to give us a Relation resulting in their union?

Hoping for some direction, or maybe even a “you’re doing it wrong.” Thanks!

-Steve @jangosteve