我最近发现了一个巧妙的技巧来替换大于/小于railswhere
中,您可以使用范围来替换where的字符串版本:
Post.where('id <= ?', 10)
可以替换为:
Post.where(id: ..10)
和三个点将其从<=
变为<
Post.where('id < ?', 10)
可以替换为:
Post.where(id: ...10)
这个技巧似乎适用于:
- 小于或等于
<=
- 小于
<
- 大于或等于
>=
然而,它不工作大于>
,因为:
Post.where(id: 10..)
和Post.where(id: 10...)
都将搜索大于或等于。
我的问题是,有没有一种方法可以让这个工作大于(除了用+ 1
破解它吗?):
Post.where(id: (10+1)..)
我的假设是答案是否定的,但我仍然想问!
下面是我正在谈论的一个完整的例子:
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]]
您可以使用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
注意Post.where(id: (10+1)..)
可以工作,因为id
是一个整数列。但是,这不适用于十进制列,例如Post.where('average_rating > 3.0')
。使用Post.where(average_rating: (3.0+1)..)
将查找平均评级>4分,但跳过了平均评分3.5分的帖子。Post.where.not(average_rating: ..3.0)
将产生正确的查询。
这个问题的主要目的是试图防止必须将长查询更改为"字符串";方法:
# 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)
如果大于需要的值,解决这个问题并使其可读的一种方法是将查询拆分为两个单独的方法:
# 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)