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