ruby-on-rails - Rails 4 查询，按条件范围从具有许多直通属性

Question

问题是:“给定一家公司，我应该如何构建返回所有事件的查询，该查询由所有员工在各自的就业期间创建强>?"

例如:

@company = Company.create

# Welcome John
@john = User.create
@event_a = @john.events.create date: Date.new(2013,6,1)
@event_b = @john.events.create date: Date.new(2014,1,1)
@company.employments.create user:@john, since: Date.new(2013,12,20)

# Welcome Jack
@jack = User.create
@event_c = @jack.events.create date: Date.new(2012,1,1)
@event_d = @jack.events.create date: Date.new(2013,1,1)
@company.employments.create user:@jack, 
    since: Date.new(2011,12,20), till:  Date.new(2012,12,31)

@company.events
=> [@event_b, @event_c]
# @event_a is not returned because it was created prior to John's hiring
# @event_d is not returned because it was created after Jack's departure

我想出了一个解决方案，但我想知道是否有任何方法可以改进它。

class Event
  belongs_to :user

  # attributes
  # date: datetime
  # …
end

class Employment
  belongs_to :user
  belongs_to :event

  # attributes
  # since: date
  # till: date
  # …
end

class Company
  has_many :employments

  def events
    Event.where employments.map do |e|
      if e.since && e.till
        "(user_id = '#{e.user_id}' AND date BETWEEN '#{e.since}' AND '#{e.till}')"
      elsif !e.since.nil?
        "(user_id = '#{e.user_id}' AND date > '#{e.since}')"
      else
        "(user_id = '#{e.user_id}')"
      end
    end.join(' OR ')
  end
end

您认为还有其他方法可以做到这一点吗？

Answer 1

Events.joins(user: :employments).where(company_id: id)我相信你可以使用这样的东西:

def events
    Event.where employments.map do |e|
        user=User.find(e.user_id)
        since = e.since
        till  = e.till || Date.parse('3100-12-31') # a day in the distant future if nil
        user.events.where('(date is null) or (date > ? and date < ?)', since, till)
    end
end

第二次尝试，在使用适当的连接连接 Event 和 Employment 模型后，您可以使用最后的 where 子句来实现您的过滤器。

在您添加了正确的一组连接和过滤器(感谢)之后，完整方法如下所示。

def events
    Events.joins(user: :employments).where(company_id: id). # <-This comes from the author, not me.
           where('employments.since is null OR 
                 (employments.since < events.date AND employments.till is null) OR
                 (employments.since < events.date AND employments.till > events.date)')

end