Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Arel Union does not support #order or #limit

#6693

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.

Reported by Steve Schwartz · April 10th, 2011 @ 08:18 PM

State: new
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. Duke
    Duke

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

    April 26th, 2011 @ 07:48 AM