Passing date range parameters to SQL/ActiveRecord query in Rails

Question

Here I have an Order and an OrderReport table. I am doing a join on it extracting some data from each of the tables.

 OrderReport.joins("INNER JOIN orders on orders.id = order_reports.for_object_id and order_reports.for_object_type = 'Order'").

group("order_reports.sales_user_id, EXTRACT(MONTH from event_at), orders.user_id,event_at").

pluck("order_reports.sales_user_id, EXTRACT(MONTH from event_at), orders.user_id,sum((cached_user_total_due - cached_sales_tax) * split_percentage), min(event_at)")

I have this very long sql + ActiveRecord query. I wanted to add an initializer and have it take in a date start and date end to make it a little more flexible for being able to pull reports from the previous year going forward.

The start and end date would be coming from the Order table which has an event_at column. I know I would start off by doing this:

  attr_reader :start_date, :end_date

  def initialize(start_date:, end_date:)
    @start_date = start_date
    @end_date = end_date 
  end

Hieu Pham · Accepted Answer · 2016-02-22 13:30:29Z

2

You can simply achieve this by defining a scope like this:

class OrderReport < ActiveRecord::Base
  scope :during, -> (start, end) {
    where('event_at >= ? AND event_at <=?', start, end)
  }
end

Then you can do your query super easy:

OrderReport.during(start_date, end_date).joins(pla pla pla)

answered Feb 22, 2016 at 13:30

Hieu Pham

6,6922 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ioannis Tziligkakis · Accepted Answer · 2016-02-22 15:07:18Z

2

You could use a scope that utilizes theActiveRecord interface using a range object in the following way:

class OrderReport
  scope :for, -> (begins, ends) { where(event_at: begins..ends) }
end

You can use pass either Date objects or string arguments to it like:

# with strings
OrderReport.for("2015-12-12", "2015-12-30")

# with date objects
begins = Date.new(2015, 12, 12)
ends   = Date.new(2015, 12, 30)
OrderReport.for(begins, ends)

This will produce an SQL query that will look like this:

SELECT * FROM 
  order_reports
WHERE
  order_reports.event_at BETWEEN '2015-12-12' AND '2015-12-30'

This way you leave Arel to deal with query building and you don't have to worry about SQL adapter combatibility.

answered Feb 22, 2016 at 15:07

Ioannis Tziligkakis

7213 silver badges17 bronze badges

Collectives™ on Stack Overflow

Passing date range parameters to SQL/ActiveRecord query in Rails

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related