Rails 6.1 adds support for where with a comparison operator

Rails 6.1 introduced support for comparison operators in where clauses, eliminating the need for raw SQL when using operators like >, <, >=, <=, and !=.

Before Rails 6.1

Previously, to use comparison operators in where clauses, you had to use raw SQL:

# Using raw SQL
User.where("age > ?", 18)
User.where("created_at >= ?", 1.week.ago)
User.where("price < ?", 100)

This approach worked but required writing SQL strings, which could be error-prone and less readable.

Rails 6.1 Solution

Rails 6.1 allows you to use comparison operators directly in where clauses:

# Using comparison operators
User.where(age: 18..)
User.where(created_at: 1.week.ago..)
User.where(price: ..100)

Supported Operators

Rails 6.1 supports several comparison operators:

Greater Than (>)

# Find users older than 18
User.where(age: 18..)

# Equivalent to:
User.where("age > ?", 18)

Less Than (<)

# Find products cheaper than $100
Product.where(price: ..100)

# Equivalent to:
Product.where("price < ?", 100)

Greater Than or Equal (>=)

# Find orders from the last week
Order.where(created_at: 1.week.ago..)

# Equivalent to:
Order.where("created_at >= ?", 1.week.ago)

Less Than or Equal (<=)

# Find items with quantity 10 or less
Item.where(quantity: ..10)

# Equivalent to:
Item.where("quantity <= ?", 10)

Range Queries

You can also use ranges for between queries:

# Find users between 18 and 65
User.where(age: 18..65)

# Equivalent to:
User.where("age >= ? AND age <= ?", 18, 65)

Real-World Examples

Finding Recent Records

# Posts from the last 7 days
Post.where(created_at: 7.days.ago..)

# Orders from this month
Order.where(created_at: Time.current.beginning_of_month..)

Price Range Queries

# Products between $10 and $100
Product.where(price: 10..100)

# Expensive products (over $1000)
Product.where(price: 1000..)

Age-Based Queries

# Adults (18 and older)
User.where(age: 18..)

# Seniors (65 and older)
User.where(age: 65..)

Combining with Other Conditions

You can combine comparison operators with other where conditions:

# Active users over 18
User.where(active: true).where(age: 18..)

# Published posts from the last week
Post.where(published: true).where(created_at: 7.days.ago..)

Benefits

Using comparison operators in where clauses provides several advantages:

  • Cleaner syntax: More readable than raw SQL strings
  • Type safety: Rails handles type conversion automatically
  • SQL injection protection: Built-in protection against SQL injection
  • Consistency: Follows Rails conventions

Edge Cases

Open-Ended Ranges

# All records greater than a value
User.where(age: 18..) # age > 18

# All records less than a value
Product.where(price: ..100) # price < 100

Excluding Boundaries

For strict comparisons (excluding the boundary), you still need raw SQL:

# Strictly greater than (not equal)
User.where("age > ?", 18)

Migration from Raw SQL

If you have existing code using raw SQL, you can migrate it:

# Before
User.where("age > ?", 18)

# After
User.where(age: 18..)

Conclusion

Rails 6.1's support for comparison operators in where clauses makes queries more readable and maintainable. This feature eliminates the need for raw SQL in many common scenarios, making your code cleaner and more Rails-idiomatic.

Rails 6.1 adds support for where with a comparison operator - Abhay Nikam