ruby-on-rails – 如何使用ActiveRelation编写UNION链？

发布时间：2020-12-16 20:29:37 所属栏目：百科来源：网络整理

导读：我需要使用ActiveRelation连接UNION的任意数量的子选项. 我有点混淆了这个的AREL,因为它似乎假定UNION是一个二进制操作. 然而： ( select_statement_a ) UNION ( select_statement_b ) UNION ( select_statement_c ) 是有效的SQL.这是否可能没有做恶心的字符

我需要使用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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!