This project is archived and is in readonly mode.

#6693 new
Steve Schwartz

Arel Union does not support #order or #limit

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

If we take two ActiveRecord::Relations and join them via union:

    rel_a = => 3)
    rel_b = => 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.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.

Comments and changes to this ticket

  • Duke

    Duke April 26th, 2011 @ 07:48 AM

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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket