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)
Top comments (0)