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 rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.
