Last Updated: February 25, 2016
· h3h

Using Postgres Functions in a Rails Model Scope

When querying Rails models on a Postgres database (though this applies equally to other engines), think of cases where you can take advantage of Postgres's built-in functions.

For instance, say I have a model—call it Auction—that has attributes for opens_at and closes_at indicating when the auction should be available for bidding.

In order to query for auctions that are currently available, I can use Postgres's now() function in a where clause instead of specifying time objects with < and > from Ruby:

scope :available, -> { where("now() BETWEEN opens_at AND closes_at") }

We should be careful not to let Rails cache these queries, though. Despite the fact that the structure of the query doesn't change, we expect the value of now() to change all the time, so we should tell Rails not to use the query cache for these:

scope :available, -> { uncached { where("now() BETWEEN opens_at AND closes_at") } }

Now I can query it like so:

>> Auction.available.count
=> 1