CODEWITHSUNDEEP
ruby-on-railspostgresql
Nerian
Nerian

Reputation: 16177

How do I query date ranges in Rails?

According to the Rails guides, this is how would you query with ranges:

http://edgeguides.rubyonrails.org/active_record_postgresql.html#range-types

## All Events on a given date
Event.where("dates @> ?::date", Date.new(2014, 2, 12))

But how do I get all the events within a daterange?

I know I can do this:

Event.where("dates && daterange(?, ?)", date1, date2)

But this daterange will default to use '[)' as the bounds. Is there a way to supply a range, instead of two dates, so that the actual bounds are taken in consideration automatically?

Notice that these are different: 

date1..date2  !=   date1...date2

So far I am doing this:

if dates.exclude_end?
  Event.where("dates && daterange(?, ?, '[)')", dates.begin, dates.end)
else
  Event.where("dates && daterange(?, ?, '[]')", dates.begin, dates.end)
end

But is there a better way? I would love something as this:

Event.where("dates && ?::daterange)", dates)

EDIT:

In my case, the dates is a daterange.

[6] pry(main)> Reservation.where(dates:     Date.current..4.days.from_now.to_date).explain

Reservation Load (0.8ms)  SELECT "reservations".* FROM "reservations" WHERE ("reservations"."dates" BETWEEN $1 AND $2)  [["dates", Mon, 30 Jan 2017], ["dates", Fri, 03 Feb 2017]]

ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  malformed range literal: "2017-01-30"

Upvotes: 1

Views: 1071

Answers (1)

pozs
pozs

Reputation: 36214

Your if logic seems solid. Maybe you could simplify it more, like

Event.where("dates && daterange(?, ?, ?)",
            dates.begin, dates.end, dates.exclude_end? ? '[)' : '[]')

This way, all your variables are bound parameters & don't have to use 2 distinct queries.

daterange is a PostgreSQL specific type and as such, it is not very well supported in ORMs. (Also, it seems that ActiveRecord binds ranges as BETWEEN expressions, so you can't bind them as values).

Upvotes: 1

Related Questions