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](http://dev.mysql.com/doc/refman/5.0/en/union.html), 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.
2. 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
--
You received this message because you are subscribed to the Google Groups "Ruby on
Rails: Core" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
