Ruby-on-rails 如何使用 postgresql 在 Rails 中执行不区分大小写的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2983687/
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
How to do case-insensitive order in Rails with postgresql
提问by brad
I am in the process of switching my development environment from sqlite3 to postgresql 8.4 and have one last hurdle.
我正在将我的开发环境从 sqlite3 切换到 postgresql 8.4 并遇到最后一个障碍。
In my original I had the following line in a helper method;
在我的原著中，我在辅助方法中有以下行；
result = Users.find(:all, :order => "name collate NOCASE")
which provided a very nice case-insensitive search. I can't replicate this for postgresql. Should be easy - any ideas?
它提供了一个非常好的不区分大小写的搜索。我无法为 postgresql 复制这个。应该很容易 - 有什么想法吗？
Thanks.
谢谢。
回答by LanceH
result = Users.find(:all, :order => "LOWER(name)")
To take a little bit from both Brad and Frank.
从布拉德和弗兰克那里拿一点。
回答by Thomas Klemm
LanecH's answeradapted for Rails 3+ (including Rails 4 and 5):
LanecH 的答案适用于 Rails 3+（包括 Rails 4 和 5）：
users = User.order('LOWER(name)')
Or create a named scope you can reuse:
或者创建一个可以重用的命名范围：
class User < ActiveRecord::Base
scope :order_by_name, -> { order('LOWER(name)') }
end
users = User.order_by_name
回答by Mario Pérez
Now with Rails 5.2 you probably will get a warning if using the accepted answer.
现在使用 Rails 5.2，如果使用已接受的答案，您可能会收到警告。
DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "LOWER(?) ASC".
弃用警告：使用非属性参数调用的危险查询方法（其参数用作原始 SQL 的方法）：“LOWER(?) ASC”。
An alternative could be relying on Arel (it's merged now into Rails):
另一种可能是依赖 Arel（它现在已合并到 Rails 中）：
results = User.order(User.arel_table['name'].lower.asc)
# results.to_sql == "SELECT \"users\".* FROM \"users\" ORDER BY LOWER(\"users\".\"name\") ASC"
回答by rfusca
Have you considered storing your column as citexttype? It really just internalizes the call to lower() as I understand it. It would be automatic for you afterwards. If there are times you need a case sensitive search, this may not be the best idea though.
您是否考虑过将您的列存储为citext类型？正如我所理解的，它实际上只是内化了对 lower() 的调用。之后它会自动为您服务。如果有时您需要区分大小写的搜索，但这可能不是最好的主意。
回答by Frank Heikens
IN SQL you could use ORDER BY LOWER(columnname), no idea how to do it in Ruby. A functional index (also on LOWER(columnname) ) will help to speed things up.
在 SQL 中，您可以使用 ORDER BY LOWER(columnname)，不知道如何在 Ruby 中执行此操作。功能索引（也在 LOWER(columnname) 上）将有助于加快速度。
