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::Relationsand join them via
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
id= 3) UNION SELECT
id= 5) )”
Ideally, we’d be able to sort and limit the resulting
UNIONresults 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.to_sql #=> “( ( SELECT
id= 3) ) UNION ( SELECT
id= 5) ) ORDER BY
created_atASC LIMIT 5 )”
Currently, two things prevent this from being possible:
Arel::Nodes:Unionnode inherits from
Arel::Nodes::Binary, with no additional methods, and as such, does not support
Arel::Nodes::Union.to_sqlmethod should be wrapping both the
:rightRelation SQL strings in parentheses, so that the
limitclauses may work on the
UNIONitself, 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
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!