The three ways to query ranges in Rails

Querying ranges in Rails comes up often enough, usually for timestamps, get me records for the last month etc.

There are three ways to write these queries. Unfortunately, each comes with a drawback, so choose carefully! :)

1. SQL string

User.where("created_at > :cutoff", cutoff: 1.month.ago)

This is relatively simple, use of named replacements can ease reading, but the drawback is that this is mistake-prone and will not raise meaningful errors if some field changes etc.

2. Infinite ranges

User.where(created_at: ..1.day.ago).to_sql
=> "SELECT * FROM \"users\" WHERE \"users\".\"created_at\" <= '2024-02-15 08:31:44'"
User.where(created_at: ...1.day.ago).select("*").to_sql
=> "SELECT * FROM \"users\" WHERE \"users\".\"created_at\" < '2024-02-15 08:31:44'"
> User.where(created_at: 1.day.ago..).select("*").to_sql
=> "SELECT * FROM \"users\" WHERE \"users\".\"created_at\" >= '2024-02-15 08:31:44'"
> User.where(created_at: 1.day.ago...).select("*").to_sql
=> "SELECT * FROM \"users\" WHERE \"users\".\"created_at\" >= '2024-02-15 08:31:44'"

Probably the cleanest way to query for a slice of a range in 2024, but did you spot it? There's one pattern-breaking drawback in that the last two examples produce equal SQL, meaning we can't get "created_at is strictly greater than a day ago" this way. This may not be a problem for timestamps, a second here or there is OK, but for more discrete data like dates or integers this can be a major mistake.

3. Arel 😱

User.where(User.arel_table[:created_at].gt(1.day.ago))
User.where(User.arel_table[:created_at].gteq(1.day.ago))
User.where(User.arel_table[:created_at].lt(1.day.ago))
User.where(User.arel_table[:created_at].lteq(1.day.ago))

Probably the most powerful approach, but some say it's using an internal API that can yet change, and it can get a bit cryptic.

Conclusion

As you can see, none of the available techniques (that I know of) is a clear winner.
Maybe the solution is to define general scopes on ApplicationRecord and use those exclusively, minimizing the surface of possible problems, YMMV.

P.S.

Stumbled across this interesting solution in Arel:

A bit of sugar:

class ApplicationRecord < ActiveRecord::Base
  def self.[](column)
    arel_table[column]
  end
end

module Arel
  module Predications
    alias_method :>, :gt
    alias_method :<, :lt
    alias_method :>=, :gteq
    alias_method :<=, :lteq
    alias_method :"!=", :not_eq
    alias_method :==, :eq
  end
end

And then this becomes possible:

where(User[:created_at] < 1.day.ago)