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

Question

I'm trying to find all Users with an id greater than 200, but I'm having some trouble with the specific syntax.

User.where(:id > 200) 

and

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

have both failed.

Any suggestions?

Answer 1

Try this

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

Arel is your friend:

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

Answer 6

Another fancy possibility is...

User.where("id > :id", id: 100)

This feature allows you to create more comprehensible queries if you want to replace in multiple places, for example...

User.where("id > :id OR number > :number AND employee_id = :employee", id: 100, number: 102, employee: 1205)

This has more meaning than having a lot of ? on the query...

User.where("id > ? OR number > ? AND employee_id = ?", 100, 102, 1205)

Answer 7

If you want a more intuitive writing, it exist a gem called squeel that will let you write your instruction like this:

User.where{id > 200}

Notice the 'brace' characters { } and id being just a text.

All you have to do is to add squeel to your Gemfile:

gem "squeel"

This might ease your life a lot when writing complex SQL statement in Ruby.

Answer 8

I often have this problem with date fields (where comparison operators are very common).

To elaborate further on Mihai's answer, which I believe is a solid approach.

To the models you can add scopes like this:

scope :updated_at_less_than, -> (date_param) { 
  where(arel_table[:updated_at].lt(date_param)) }

... and then in your controller, or wherever you are using your model:

result = MyModel.updated_at_less_than('01/01/2017')

... a more complex example with joins looks like this:

result = MyParentModel.joins(:my_model).
  merge(MyModel.updated_at_less_than('01/01/2017'))

A huge advantage of this approach is (a) it lets you compose your queries from different scopes and (b) avoids alias collisions when you join to the same table twice since arel_table will handle that part of the query generation.

Answer 9

For Ruby 2.6 can accept ranges like:

# => 2.6
User.where(id: 201..)
# < 2.6
User.where(id: 201..Float::INFINITY)

Answer 10

Shorter:

User.where("id > 200")