Arel Union does not support #order or #limit #1015

Closed
lighthouse-import opened this Issue May 16, 2011 · 1 comment

Comments

Projects
None yet
1 participant

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6693
Created by Steve Schwartz - 2011-04-21 11:26:42 UTC

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.
  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.

Imported from Lighthouse.
Comment by Duke - 2011-04-26 06:48:36 UTC

I need this also. Interleaving ordering by post date and last commented date. I must do an ordered union.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment