How do I write a UNION chain with ActiveRelation?

Question

I need to be able to chain an arbitrary number of sub-selects with UNION using ActiveRelation.

I'm a little confused by the ARel implementation of this, since it seems to assume UNION is a binary operation.

However:

( select_statement_a ) UNION ( select_statement_b ) UNION ( select_statement_c )

is valid SQL. Is this possible without doing nasty string-substitution?

Gymnastic

Answer 1

You can do a bit better than what Adam Lassek has proposed though he is on the right track. I've just solved a similar problem trying to get a friends list from a social network model. Friends can be aquired automatically in various ways but I would like to have an ActiveRelation friendly query method that can handle further chaining. So I have

class User
    has_many :events_as_owner, :class_name => "Event", :inverse_of => :owner, :foreign_key => :owner_id, :dependent => :destroy
    has_many :events_as_guest, :through => :invitations, :source => :event

      def friends


        friends_as_guests = User.joins{events_as_guest}.where{events_as_guest.owner_id==my{id}}
        friends_as_hosts  = User.joins{events_as_owner}.joins{invitations}.where{invitations.user_id==my{id}}

        User.where do
          (id.in friends_as_guests.select{id}
          ) | 
          (id.in friends_as_hosts.select{id}
          )
        end
       end

end

which takes advantage of Squeels subquery support. Generated SQL is

SELECT "users".* 
FROM   "users" 
WHERE  (( "users"."id" IN (SELECT "users"."id" 
                           FROM   "users" 
                                  INNER JOIN "invitations" 
                                    ON "invitations"."user_id" = "users"."id" 
                                  INNER JOIN "events" 
                                    ON "events"."id" = "invitations"."event_id" 
                           WHERE  "events"."owner_id" = 87) 
           OR "users"."id" IN (SELECT "users"."id" 
                               FROM   "users" 
                                      INNER JOIN "events" 
                                        ON "events"."owner_id" = "users"."id" 
                                      INNER JOIN "invitations" 
                                        ON "invitations"."user_id" = 
                                           "users"."id" 
                               WHERE  "invitations"."user_id" = 87) )) 

An alternative pattern where you need a variable number of components is demonstrated with a slight modification to the above code

  def friends


    friends_as_guests = User.joins{events_as_guest}.where{events_as_guest.owner_id==my{id}}
    friends_as_hosts  = User.joins{events_as_owner}.joins{invitations}.where{invitations.user_id==my{id}}

    components = [friends_as_guests, friends_as_hosts]

    User.where do
      components = components.map { |c| id.in c.select{id} }
      components.inject do |s, i|
        s | i
      end
    end


  end

And here is a rough guess as to the solution for the OP's exact question

class Shift < ActiveRecord::Base
  def self.limit_per_day(options = {})
    options[:start]   ||= Date.today
    options[:stop]    ||= Date.today.next_month
    options[:per_day] ||= 5

    queries = (options[:start]..options[:stop]).map do |day|

      where{|s| s.scheduled_start >= day}.
      where{|s| s.scheduled_start < day.tomorrow}.
      limit(options[:per_day])

    end

    where do
      queries.map { |c| id.in c.select{id} }.inject do |s, i|
        s | i
      end
    end
  end
end

Athwartships

This is a slightly different case, however. I need to be able to chain an arbitrary number of selects together. So I will still have to resort to string interpolation regardless of whether I use UNION or OR. Unless you can suggest a way to accomplish that with Squeel/ARel? – Gymnastic

That can be done. It just requires a little squeel magic. Let me think about it. Probably could be done with Enumerable inject. I'll give it a go and update the answer if it works. – Athwartships

Ok. I've made an equivalent function that puts the components into an array first and then combines those components. Just remember SQueel is just plain ruby with some method missing magic. You can do normal ruby inside the where blocks and build queries dynamically. – Athwartships

queries.map{...}.reduce(:|) would also work and it looks like a smiley. – Omphale

@Athwartships generated sql part 1 will break for oracle if the inner query returns more than 1000 rows – Ellerd

Answer 2

Because of the way the ARel visitor was generating the unions, I kept getting SQL errors while using Arel::Nodes::Union. Looks like old-fashioned string interpolation was the only way to get this working.

I have a Shift model, and I want to get a collection of shifts for a given date range, limited to five shifts per day. This is a class method on the Shift model:

def limit_per_day(options = {})
  options[:start]   ||= Date.today
  options[:stop]    ||= Date.today.next_month
  options[:per_day] ||= 5

  queries = (options[:start]..options[:stop]).map do |day|

    select{id}.
    where{|s| s.scheduled_start >= day}.
    where{|s| s.scheduled_start < day.tomorrow}.
    limit(options[:per_day])

  end.map{|q| "( #{ q.to_sql } )" }

  where %{"shifts"."id" in ( #{queries.join(' UNION ')} )}
end

(I am using Squeel in addition to ActiveRecord)

Having to resort to string-interpolation is annoying, but at least the user-provided parameters are being sanitized correctly. I would of course appreciate suggestions to make this cleaner.

Gymnastic

Answer 3

I like Squeel. But don't use it. So I came to this solution (Arel 4.0.2)

def build_union(left, right)
  if right.length > 1
    Arel::Nodes::UnionAll.new(left, build_union(right[0], right[1..-1]))
  else
    Arel::Nodes::UnionAll.new(left, right[0])
  end
end

managers = [select_manager_1, select_manager_2, select_manager_3]
build_union(managers[0], managers[1..-1]).to_sql
# => ( (SELECT table1.* from table1)
#    UNION ALL
#    ( (SELECT table2.* from table2)
#    UNION ALL
#    (SELECT table3.* from table3) ) )

Polytypic

It seems they added Arel::Nodes::UnionAll for non-binary unions. I'll revisit this when I can use Arel 4. – Gymnastic

doesn't work for me I get Cannot visit ModelName::ActiveRecord_Relation – Caroncarotene

Answer 4

There's a way to make this work using arel:

tc=TestColumn.arel_table
return TestColumn.where(tc[:id]
           .in(TestColumn.select(:id)
                         .where(:attr1=>true)
                         .union(TestColumn.select(:id)
                                          .select(:id)
                                          .where(:attr2=>true))))

Piotrowski

I don't think you understood the question. That is a single UNION between two queries, which works fine. I need to be able to chain UNIONs together to arbitrary length. It's possible that UNION semantics are different between SQL implementations -- in this case I am using PostgreSQL. – Gymnastic

I did, this is the closest I came to performing an union using arel. – Piotrowski

Anyway, you can get an Arel::Node by performing Domain.where(attr=>value).union(Domain.where(attr2=>value)) maybe you can afterwards the to_sql method and pass it to find_by_sql – Piotrowski