Databases
StackOverflow2013
Postsdbo
POUsing scope to return results within multiple DateTime ranges in ActiveRecord
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing scope to return results within multiple DateTime ranges in ActiveRecord
    primarykey
    data
    text
    <p>I've got a <code>Session</code> model that has a <code>:created_at</code> date and a <code>:start_time</code> date, both stored in the database as <code>:time</code>. 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:</p> <pre><code>class Session &lt; ActiveRecord::Base ... scope :filter_by_date, lambda { |date| date = date.split(",")[0] where(:created_at =&gt; 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 =&gt; DateTime.strptime("#{date} #{from[:digits]} #{from[:meridian]}", '%m/%d/%Y %r').. DateTime.strptime("#{date} #{to[:digits]} #{to[:meridian]}", '%m/%d/%Y %r') ) } end </code></pre> <p>The controller looks more or less like this:</p> <pre><code>class SessionController &lt; ApplicationController def index if params.include?(:date) || params.include?(:time) &amp;&amp; ( params[:time][:from][:digits].present? &amp;&amp; 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! &amp;params[:sort].to_sym if params[:sort].present? else @items = Session.find(:all, :order =&gt; :created_at) end end end </code></pre> <p>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. <code>"07/12/2012,07/13/2012,07/17/2012"</code>, 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.</p> <p>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?</p> <p>My closest guess looks like this but it's not returning anything so I know it's wrong.</p> <pre><code>scope :filter_by_date, lambda { |date| date = date.split(",") date.each do |i| where(:created_at =&gt; 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 =&gt; DateTime.strptime("#{i} #{from[:digits]} #{from[:meridian]}", '%m/%d/%Y %r').. DateTime.strptime("#{i} #{to[:digits]} #{to[:meridian]}", '%m/%d/%Y %r') ) end } </code></pre> <p>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!</p> <hr> <p><strong>EDIT:</strong></p> <p>Here's the solution I've come up with by combining the advice of Kenichi and Chuck Vose below:</p> <pre><code>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 &lt;&lt; '(created_at &gt;= ? AND created_at &lt;= ?)' 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) &gt;= ? AND CAST(start_time AS TIME) &lt;= ?", *args) } </code></pre> <p>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!</p> <p>An important point I overlooked is that the <code>where</code> 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNate Goldman
    1. USNate Goldman
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing scope to return results within multiple DateTime ranges in ActiveRecord
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing scope to return results within multiple DateTime ranges in ActiveRecord
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUsing scope to return results within multiple DateTime ranges in ActiveRecord
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload