Rails: How to Use Greater Than/Less Than in Active Record where Statements
For example, if I want to find all users created within the last week, I would
ask for all users from my User model in which the column updated_at is
greater than 1 week ago, or 1.week.ago.
1. Using Ruby’s Infinity Range (Best)
In Rails 5+ and Ruby 2.6+, the infinity range can be used like so:
User.where(created_at: 1.week.ago..)If you haven’t upgraded to Ruby 2.6 yet, you can specify the end of the range as
the constant Float::INFINITY:
User.where(created_at: 1.week.ago..Float::INFINITY)This is the best option because it is the closest syntax to actuality.
From 1 week ago until infinity == greater than or equal to 1 week ago
It also doesn’t have any of the caveats that using a raw string has (explained later in this post)
2. Using Active Record’s Arel Table
Utilizing arel directly, we can signal to ActiveRecord the logical SQL we want to do functionally:
User.where(User.arel_table[:created_at].gt(1.week.ago))When faced with a challenging ActiveRecord Query that doesn’t fit into the core
ruby-style syntax, I typically turn to Arel to provide me with a functional
core and extra safety.
3. Using a Raw String (Worst)
In ActiveRecord, it’s possible to utilize a raw string to access sql’s interface directly, like so:
User.where('users.created_at > ?', 1.week.ago)Or even the keyword interpolation style:
User.where('users.created_at > :after_date', after_date: 1.week.ago)While this works, using SQL strings with ActiveRecord isn’t always the best due to:
- Higher risk of accidental sql injection vulnerabilities
- Reduced database adapter compatibility