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  gt  200     and   User where     gt  200    id     have both failed   Any suggestions

User · Answer

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.

User · Answer

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  gt    9  Post where  id  gt     9  Post where  id  lt    3  Post where  id  lt     3   So your query can be rewritten as follows  User where  id  gt    200    Here is a link to PR where you can find more examples

User · Answer

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   gt   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    gt   vs  gt   To avoid people having to dig through and follow the comments conversation here are the highlights   The method above only generates a  gt   query and not a  gt   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  gt  200 but actually look for id  gt   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  gt  y      x  lt   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   lt   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   gt  200     The specifics are as follows   User arel table                 gt  an Arel  Table instance for the User model   users table User arel table  id             gt  an Arel  Attributes  Attribute for the id column User arel table  id  gt 200     gt  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   gt    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

User · Answer

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  gt  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

User · Answer

Another fancy possibility is     User where  id  gt   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  gt   id OR number  gt   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  gt    OR number  gt    AND employee id       100  102  1205

User · Answer

Arel is your friend  User where User arel table  id  gt 200

User · Answer

Try this  User where  id  gt      200

User · Answer

A better usage is to create a scope in the user model where arel table  id  gt id

User · Answer

Shorter   User where  id  gt  200

[ruby-on-rails] Rails: Using greater than/less than with a where statement

Examples related to ruby-on-rails

Examples related to syntax

Examples related to where