Geek asks and answers

Using scope to return results in multiple DateTime ranges in ActiveRecord

I have a model Sessionwith a date :created_atand a date :start_timestored in the database as :time. I am currently spitting out a bunch of results on a huge table and letting users filter results for a single date and extra time range using scope:

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 something 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 18:30 19:30.

, , . ? ?

, , , .

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
}

, DateTime, , , , 18:30 19:30 , - . , , , , . , !

EDIT:

, , Kenichi Chuck Vose :

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

, , . !

, , , where , , . ! .

+5
2

- :

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
}
+4

, - . DateTimes , :

CAST(datetime_col AS TIME)

, , :

i.where("CAST(start_time AS TIME) IN(?)", times.join(", "))

, , . , , - i.to_sql, , . , , , , AND. , , 7/12, 7/13 7/21.

, , : sql- stritimes.

, #{} . , , . , ?, , .

-, strptime . , . , , , gsub/into - , . MySQL , m/d/y anyways. , DateTime, : Date.new(2001,2,3) .

+1

More articles:


All Articles