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 viaunion
:
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
.* FROMmy_models
WHERE (my_models
.id
= 3) UNION SELECTmy_models
.* FROMmy_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
.* FROMmy_models
WHERE (my_models
.id
= 3) ) UNION ( SELECTmy_models
.* FROMmy_models
WHERE (top_items
.id
= 5) ) ORDER BYcreated_at
ASC LIMIT 5 )”
Currently, two things prevent this from being possible:
- The
Arel::Nodes:Union
node inherits fromArel::Nodes::Binary
, with no additional methods, and as such, does not supportorder
orlimit
methods.
- The
Arel::Nodes::Union.to_sql
method should be wrapping both the:left
and the:right
Relation SQL strings in parentheses, so that theorder
andlimit
clauses may work on theUNION
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