Ruby-on-rails Rails：使用大于/小于 where 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11317662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails: Using greater than/less than with a where statement
提问by Adam Templeton
I'm trying to find all Users with an id greater than 200, but I'm having some trouble with the specific syntax.
我正在尝试查找 ID 大于 200 的所有用户，但是我在使用特定语法时遇到了一些问题。
User.where(:id > 200)
and
和
User.where("? > 200", :id)
have both failed.
都失败了。
Any suggestions?
有什么建议？
回答by RadBrad
Try this
尝试这个
User.where("id > ?", 200)
回答by Aaron
I've only tested this in Rails 4 but there's an interesting way to use a range with a wherehash to get this behavior.
我只在 Rails 4 中测试过这个，但是有一种有趣的方法可以使用带有where散列的范围来获得这种行为。
User.where(id: 201..Float::INFINITY)
will generate the SQL
将生成 SQL
SELECT `users`.* FROM `users` WHERE (`users`.`id` >= 201)
The same can be done for less than with -Float::INFINITY.
可以用 less than with 来做同样的事情-Float::INFINITY。
I just posted a similar question asking about doing this with dates here on SO.
我刚刚发布了一个类似的问题，询问有关在 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 nota >. There are many ways to handle this alternative.
上面的方法只生成一个>=查询而不是一个>. 有很多方法可以处理这种替代方法。
For discrete numbers
对于离散数
You can use a number_you_want + 1strategy like above where I'm interested in Users with id > 200but actually look for id >= 201. This is fine for integers and numbers where you can increment by a single unit of interest.
您可以使用number_you_want + 1类似上面的策略，我对用户感兴趣id > 200但实际上寻找id >= 201. 这适用于整数和数字，您可以增加一个感兴趣的单位。
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.
我们可以使用事实，x > y == !(x <= y)并使用 where not 链。
User.where.not(id: -Float::INFINITY..200)
which generates the SQL
生成 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 + 1strategy.
这需要额外的一秒钟来阅读和推理，但适用于您无法使用该+ 1策略的非离散值或列。
Arel table
阿雷尔表
If you want to get fancy you can make use of the Arel::Table.
如果你想变得花哨，你可以使用Arel::Table.
User.where(User.arel_table[:id].gt(200))
will generate the SQL
将生成 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 exactSQL 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.
这种方法将为您提供您感兴趣的确切SQL，但是没有多少人直接使用 Arel 表，并且会发现它很混乱和/或令人困惑。您和您的团队会知道什么最适合您。
Bonus
奖金
Starting in Rails 5 you can also do this with dates!
从 Rails 5 开始，您也可以使用日期来执行此操作！
User.where(created_at: 3.days.ago..DateTime::Infinity.new)
will generate the SQL
将生成 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::INFINITYyou'll be able to just write 201... More info in this blog post.
Ruby 2.6 发布后（2018 年 12 月 25 日），您将能够使用新的无限范围语法！而不是201..Float::INFINITY你将能够只写201... 这篇博文中有更多信息。
回答by Mihai
A better usage is to create a scope in the user model where(arel_table[:id].gt(id))
更好的用法是在用户模型中创建作用域 where(arel_table[:id].gt(id))
回答by Douglas
If you want a more intuitive writing, it exist a gem called squeelthat will let you write your instruction like this:
如果你想要一个更直观的写作，它有一个叫做squeel的 gem，它可以让你像这样写你的指令：
User.where{id > 200}
Notice the 'brace' characters { } and idbeing just a text.
注意“大括号”字符 { } 并且id只是一个文本。
All you have to do is to add squeel to your Gemfile:
你所要做的就是在你的 Gemfile 中添加 squeel：
gem "squeel"
This might ease your life a lot when writing complex SQL statement in Ruby.
在 Ruby 中编写复杂的 SQL 语句时，这可能会让您的生活变得轻松很多。
回答by joegiralt
Arel is your friend.
阿瑞尔是你的朋友。
User.where(User.arel_table[:id].gt(200))
User.where(User.arel_table[:id].gt(200))
回答by Victor
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)
回答by Brett Green
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.
进一步详细说明 Mihai 的回答，我认为这是一个可靠的方法。
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.
这种方法的一个巨大优势是 (a) 它允许您从不同的范围组合查询，并且 (b) 当您连接到同一个表两次时避免别名冲突，因为 arel_table 将处理查询生成的那部分。
回答by Bengala
Shorter:
更短：
User.where("id > 200")
