Rails: using larger or smaller than with where clause

Question

I am trying to find all users with an identifier of more than 200, but I am having some problems with the specific syntax.

User.where(:id > 200) 

and

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

both refused.

Any suggestions?

Answer 1

try it

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

Answer 2

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

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

will generate 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 to do this with dates here on SO .

>= against >

So that people do not dig and do not follow the comments, here are the main points.

The method above only generates the request >= and not > . There are many ways to deal with this alternative.

For discrete numbers

You can use the strategy number_you_want + 1 as described above, where I am interested in users with id > 200 but actually looking for id >= 201 . This is good for integers and numbers, where you can increase by a unit of interest.

If you have a number extracted into a well-named constant, this might be 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 SQL

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

It takes an extra second to read and ponder, but will work for non-discrete values ​​or columns where you cannot use the + 1 strategy.

Arel table

If you want to become trendy, you can use Arel::Table .

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

will generate SQL

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

Features 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 help you get exactly the SQL that interests you, but not many people use the Arel table directly and may find it confusing and / or confusing. You and your team will know what is best for you.

bonus

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

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

will generate SQL

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

Double Bonus

After the release of Ruby 2.6 (December 25, 2018), you can use the new endless range syntax! Instead of 201..Float::INFINITY you can just write 201.. More information in this post .

Answer 3

The best use is to create a region in the user model where(arel_table[:id].gt(id))

Answer 4

If you want a more intuitive letter, it has a gem called squeel that allows you to write your instructions as follows:

 User.where{id > 200} 

Note that the brackets {} and id are just text.

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

 gem "squeel" 

This can make your life easier when writing a complex SQL statement in Ruby.

Answer 5

Arel is your friend.

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

Answer 6

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

We will dwell in more detail on the answer of Mihai, who, in my opinion, is a reliable approach.

You can add the following areas to the models:

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

... and then in your controller, or where you use your model:

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

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

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

The huge advantage of this approach is that (a) it allows you to compose queries from different areas and (b) avoid collisions of aliases when you join the same table twice since arel_table will handle this part of the query generation.

Answer 7

Another bizarre opportunity ...

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

This function allows you to create more understandable queries if you want to replace in several places, for example ...

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

Does it matter more than have a lot ? upon request ...

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

Answer 8

In short:

 User.where("id > 200")