ruby-on-rails - 如何编写带有ActiveRelation的UNION链？

我需要能够使用ActiveRelation用UNION链接任意数量的子选择。

我对ARel的实现感到有些困惑，因为它似乎假定UNION是二进制操作。

然而：

( select_statement_a ) UNION ( select_statement_b ) UNION ( select_statement_c )

是有效的SQL。如果不进行讨厌的字符串替换，是否有可能？

最佳答案

尽管亚当·拉塞克（Adam Lassek）处在正确的轨道上，但您可以做得比他建议的要好。我刚刚解决了一个类似的问题，试图从社交网络模型中获取朋友列表。可以通过多种方式自动获取好友，但是我希望拥有一个ActiveRelation友好的查询方法，可以处理进一步的链接。所以我有

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

利用Squeels子查询支持。生成的SQL是

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

通过对上面的代码稍作修改，演示了需要可变数量组件的替代模式

  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

这是关于OP确切问题的解决方案的粗略猜测

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

关于ruby-on-rails - 如何编写带有ActiveRelation的UNION链？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8045823/