Rails "Where" method - greater than (">") using endless ranges

user_12054303 0
Viewed 684

I just recently discovered a neat trick to replace greater than / less than in rails where in which you can use a range to replace the string version of where:

Post.where('id <= ?', 10)

can be replaced with:

Post.where(id: ..10)

and three dots changes it from <= to <

Post.where('id < ?', 10)

can be replaced with:

Post.where(id: ...10)

this trick seems to work for:

  • less than or equal to <=
  • less than <
  • greater than or equal to >=

HOWEVER it does not work for greater than > because:

Post.where(id: 10..) and Post.where(id: 10...) will both search for greater than or equal to.

My question is, is there a way to get this to work for greater than (other than hacking it with + 1?):

Post.where(id: (10+1)..)

My assumption is that the answer is no, but I still wanted to ask!

Here is a full example of what I am talking about:


Post.where(id: ..9)
=>   Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" <= $1  [["id", 9]]

Post.where(id: ...9)
=>   Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" < $1  [["id", 9]]

Post.where(id: 1..)
=>   Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" >= $1  [["id", 1]]

# NOTE: THIS STILL ONLY GOES >=, NOT > 
Post.where(id: 1...)
=>   Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" >= $1  [["id", 1]]
range activerecord ruby-on-rails
2 Answers

You can use where.not

Post.where.not(id: ..10).to_sql
# => SELECT "posts".* FROM "posts" WHERE "posts"."id" > 10

Post.where.not(id: ...10).to_sql
# => SELECT "posts".* FROM "posts" WHERE "posts"."id" >= 10

Note that Post.where(id: (10+1)..) works because the id is an integer column. However, this will not work for decimal columns, such as Post.where('average_rating > 3.0'). Using Post.where(average_rating: (3.0+1)..) will look for average ratings > 4 but skips posts with average rating of 3.5. Post.where.not(average_rating: ..3.0) will result in the correct query.

user_26971830

The main purpose of the question is trying to prevent having to change a long query into the "string" approach:

# good
Post.where(name: name, type: type, whatever: whatever)

# bad - uses string approach
Post.where('name = :name AND type = :type AND whatever = :whatever',
           name: name, type: type, whatever: whatever)

If greater than is needed, A way to get around this AND allow it to be readable, is to split the query into 2 separate where methods:

# good - only uses string approach with greater than which is more 
# readable that where.not
Post.where(name: name, type: type, whatever: whatever).where('id > ?', 10)

# bad - uses string method
Post.where('name = :name AND type = :type AND whatever = :whatever AND id > :id',
           name: name, type: type, whatever: whatever, id: 10)

# bad - where.not is not very readable
Post.where(name: name, type: type, whatever: whatever).where.not(id: ..10)
user_120543030
Related