DEV Community

Augusts Bautra
Augusts Bautra

Posted on

4

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)
Enter fullscreen mode Exit fullscreen mode

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'"
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And then this becomes possible:

where(User[:created_at] < 1.day.ago)
Enter fullscreen mode Exit fullscreen mode

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay