使用范围中的ActiveRecord内的多个日期时间范围内返回结果 [英] Using scope to return results within multiple DateTime ranges in ActiveRecord

问题描述

我有一个会话模型，有一个：created_at 日期和 ：START_TIME 迄今为止，无论是存储在数据库中的：时间。目前，我吐出了一堆的结果产生巨大的表，并允许用户使用范围由单一的日期和时间可选范围筛选结果，像这样：

I've got a Session model that has a :created_at date and a :start_time date, both stored in the database as :time. I'm currently spitting out a bunch of results on an enormous table and allowing users to filter results by a single date and an optional range of time using scopes, like so:

class Session < ActiveRecord::Base
  ...

  scope :filter_by_date, lambda { |date|
    date = date.split(",")[0]
    where(:created_at =>
      DateTime.strptime(date, '%m/%d/%Y')..DateTime.strptime(date, '%m/%d/%Y').end_of_day
    )
  }
  scope :filter_by_time, lambda { |date, time|
    to = time[:to]
    from = time[:from]
    where(:start_time =>
      DateTime.strptime("#{date} #{from[:digits]} #{from[:meridian]}", '%m/%d/%Y %r')..
      DateTime.strptime("#{date} #{to[:digits]} #{to[:meridian]}", '%m/%d/%Y %r')
    )
  }

end

控制器看起来或多或少是这样的：

The controller looks more or less like this:

class SessionController < ApplicationController

  def index
    if params.include?(:date) ||
       params.include?(:time) &&
     ( params[:time][:from][:digits].present? && params[:time][:to][:digits].present? )

      i = Session.scoped
      i = i.filter_by_date(params[:date]) unless params[:date].blank?
      i = i.filter_by_time(params[:date], params[:time]) unless params[:time].blank? || params[:time][:from][:digits].blank? || params[:time][:to][:digits].blank?

      @items = i
      @items.sort_by! &params[:sort].to_sym if params[:sort].present?
    else
      @items = Session.find(:all, :order => :created_at)
    end
  end

end

我需要让用户过滤使用多个日期的结果。我收到的PARAMS作为一个逗号分隔的列表中的字符串格式，如： 07/12 / 2012,07 / 13 / 2012,07 / 17/2012，并且必须能够在数据库中查询几个不同的日期范围和时间这些日期范围内的范围，并合并这些结果，因此，例如所有的7/12，7/13和7/17下午6:30和晚上7:30之间的会话

I need to allow users to filter results using multiple dates. I'm receiving the params as a comma-separated list in string format, e.g. "07/12/2012,07/13/2012,07/17/2012", and need to be able to query the database for several different date ranges, and time ranges within those date ranges, and merge those results, so for example all of the sessions on 7/12, 7/13 and 7/17 between 6:30 pm and 7:30 pm.

我一直在到处找，并尝试一些不同的东西，但我不知道如何真正做到这一点。这可能使用范围？如果不是出了什么做到这一点的最好方法是什么？

I have been looking everywhere and have tried several different things but I can't figure out how to actually do this. Is this possible using scopes? If not what's the best way to do this?

我最亲密的猜测是这样的，但它不返回任何东西，所以我知道这是错的。

My closest guess looks like this but it's not returning anything so I know it's wrong.

scope :filter_by_date, lambda { |date|
  date = date.split(",")
  date.each do |i|
    where(:created_at =>
      DateTime.strptime(i, '%m/%d/%Y')..DateTime.strptime(i, '%m/%d/%Y').end_of_day
    )
  end
}
scope :filter_by_time, lambda { |date, time|
  date = date.split(",")
  to = time[:to]
  from = time[:from]
  date.each do |i|
    where(:start_time =>
      DateTime.strptime("#{i} #{from[:digits]} #{from[:meridian]}", '%m/%d/%Y %r')..
      DateTime.strptime("#{i} #{to[:digits]} #{to[:meridian]}", '%m/%d/%Y %r')
    )
  end
}

另一个难题是，开始时间都存储为日期时间对象，因此他们已经有一个固定的日期，所以如果我想返回所有会话下午6:30和下午7:30之间开始了我需要的身影任何日期别的东西出来了。第三方负责的数据，所以我不能改变它是如何结构或贮存，我只需要弄清楚如何做所有这些复杂的查询。请帮帮忙！

Another complication is that the start times are all stored as DateTime objects so they already include a fixed date, so if I want to return all sessions started between 6:30 pm and 7:30 pm on any date I need to figure something else out too. A third party is responsible for the data so I can't change how it's structured or stored, I just need to figure out how to do all these complex queries. Please help!

编辑：

下面是我想出的解决方案相结合，研一和Chuck Vose公司的建议如下：

Here's the solution I've come up with by combining the advice of Kenichi and Chuck Vose below:

scope :filter_by_date, lambda { |dates|
  clauses = []
  args = []
  dates.split(',').each do |date|
    m, d, y = date.split '/'
    b = "#{y}-#{m}-#{d} 00:00:00"
    e = "#{y}-#{m}-#{d} 23:59:59"
    clauses << '(created_at >= ? AND created_at <= ?)'
    args.push b, e
  end
  where clauses.join(' OR '), *args
}

scope :filter_by_time, lambda { |times|
  args = []
  [times[:from], times[:to]].each do |time|
    h, m, s = time[:digits].split(':')
    h = (h.to_i + 12).to_s if time[:meridian] == 'pm'
    h = '0' + h if h.length == 1
    s = '00' if s.nil?
    args.push "#{h}:#{m}:#{s}"
  end
  where("CAST(start_time AS TIME) >= ? AND
         CAST(start_time AS TIME) <= ?", *args)
}

该解决方案允许我返回从多个不连续的日期会或返回一个时间范围内的任何会议，但无依赖的日期可言，或者二者结合起来作用域这些日期内不连续的日期和时间来过滤。耶！

This solution allows me to return sessions from multiple non-consecutive dates OR return any sessions within a range of time without relying on dates at all, OR combine the two scopes to filter by non-consecutive dates and times within those dates. Yay!

这是很重要的一点我忽略的是，其中，语句必须放在最后 - 保持它的每个循环返回里面什么都没有。感谢您的帮助你们俩！我觉得更聪明了。

An important point I overlooked is that the where statement must come last -- keeping it inside of an each loop returns nothing. Thanks to both of you for all your help! I feel smarter now.

推荐答案

是这样的：

scope :filter_by_date, lambda { |dates|
  clauses = []
  args = []
  dates.split(',').each do |date|
    m, d, y = date.split '/'
    b = "#{y}-#{m}-#{d} 00:00:00"
    e = "#{y}-#{m}-#{d} 23:59:59"
    clauses << '(start_time >= ? AND start_time <= ?)'
    args.push b, e
  end
  where clauses.join(' OR '), *args
}

和

scope :filter_by_time, lambda { |dates, time|
  clauses = []
  args = []
  dates.split(',').each do |date|
    m, d, y = date.split '/'
    f = time[:from] # convert to '%H:%M:%S'
    t = time[:to]   # again, same
    b = "#{y}-#{m}-#{d} #{f}"
    e = "#{y}-#{m}-#{d} #{t}"
    clauses << '(start_time >= ? AND start_time <= ?)'
    args.push b, e
  end
  where clauses.join(' OR '), *args
}

这篇关于使用范围中的ActiveRecord内的多个日期时间范围内返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！