ruby-on-railsactiverecordrails-activerecord

Reputation: 4617

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

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?

Upvotes: 227

Answers (10)

Aaron

Reputation: 14019

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.

Upvotes: 273

Mosaaleb

Reputation: 1089

For Ruby 2.6 can accept ranges like:

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

Upvotes: 4

Marian13

Reputation: 9238

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.

Upvotes: 23

joegiralt

Reputation: 310

Arel is your friend:

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

Upvotes: 11

Victor Cordeiro Costa

Reputation: 2184

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)

Upvotes: 7

Brett Green

Reputation: 3755

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.

Upvotes: 4