Rails: Using greater than/less than with a where statement

Question

Try this

User.where("id > ?", 200) 

State of the Art

Ruby 2.7 introduced beginless ranges which makes specifying >, < and their inclusive cousins (>= and <=) even easier.

User.where(id: 200..).to_sql
  => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" >= 200"
# There is no difference w/ a non-inclusive endless range (e.g. `200...`)

User.where(id: ..200).to_sql
  => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" <= 200"
User.where(id: ...200).to_sql
  => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" < 200"

This also works perfectly with timestamps!

User.where(created_at: 1.day.ago..).to_sql
  => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"created_at\" >= '2021-09-12 15:38:32.665061'"
User.where(created_at: ..1.day.ago).to_sql
  => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"created_at\" <= '2021-09-12 15:38:37.756870'"

Original Answer & Updates

I've only tested this in Rails 4 but there's an interesting way to use a range with a where hash to get this behavior.

User.where(id: 201..Float::INFINITY)

will generate the SQL

SELECT `users`.* FROM `users`  WHERE (`users`.`id` >= 201)

The same can be done for less than with -Float::INFINITY.

I just posted a similar question asking about doing this with dates here on SO.

>= vs >

To avoid people having to dig through and follow the comments conversation here are the highlights.

The method above only generates a >= query and not a >. There are many ways to handle this alternative.

For discrete numbers

You can use a number_you_want + 1 strategy like above where I'm interested in Users with id > 200 but actually look for id >= 201. This is fine for integers and numbers where you can increment by a single unit of interest.

If you have the number extracted into a well named constant this may be the easiest to read and understand at a glance.

Inverted logic

We can use the fact that x > y == !(x <= y) and use the where not chain.

User.where.not(id: -Float::INFINITY..200)

which generates the SQL

SELECT `users`.* FROM `users` WHERE (NOT (`users`.`id` <= 200))

This takes an extra second to read and reason about but will work for non discrete values or columns where you can't use the + 1 strategy.

Arel table

If you want to get fancy you can make use of the Arel::Table.

User.where(User.arel_table[:id].gt(200))

will generate the SQL

"SELECT `users`.* FROM `users` WHERE (`users`.`id` > 200)"

The specifics are as follows:

User.arel_table              #=> an Arel::Table instance for the User model / users table
User.arel_table[:id]         #=> an Arel::Attributes::Attribute for the id column
User.arel_table[:id].gt(200) #=> an Arel::Nodes::GreaterThan which can be passed to `where`

This approach will get you the exact SQL you're interested in however not many people use the Arel table directly and can find it messy and/or confusing. You and your team will know what's best for you.

Bonus

Starting in Rails 5 you can also do this with dates!

User.where(created_at: 3.days.ago..DateTime::Infinity.new)

will generate the SQL

SELECT `users`.* FROM `users` WHERE (`users`.`created_at` >= '2018-07-07 17:00:51')

Double Bonus

Once Ruby 2.6 is released (December 25, 2018) you'll be able to use the new infinite range syntax! Instead of 201..Float::INFINITY you'll be able to just write 201... More info in this blog post.

A better usage is to create a scope in the user model where(arel_table[:id].gt(id))

Update

Rails core team decided to revert this change for a while, in order to discuss it in more detail. See this comment and this PR for more info.

I am leaving my answer only for educational purposes.

---

new 'syntax' for comparison in Rails 6.1 (Reverted)

Rails 6.1 added a new 'syntax' for comparison operators in where conditions, for example:

Post.where('id >': 9)
Post.where('id >=': 9)
Post.where('id <': 3)
Post.where('id <=': 3)

So your query can be rewritten as follows:

User.where('id >': 200) 

Here is a link to PR where you can find more examples.

Arel is your friend:

User.where(User.arel_table[:id].gt(200))