如何使用 postgresql 在 Rails 中执行不区分大小写的顺序
我正在将我的开发环境从 sqlite3 切换到 postgresql 8.4,并且还有最后一个障碍。
在我的原文中,我在辅助方法中有以下行;
result = Users.find(:all, :order => "name collate NOCASE")
这提供了一个非常好的不区分大小写的搜索。我无法为 postgresql 复制这个。应该很容易 - 有什么想法吗?
谢谢。
I am in the process of switching my development environment from sqlite3 to postgresql 8.4 and have one last hurdle.
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?
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
从布拉德和弗兰克那里吸取一些教训。
To take a little bit from both Brad and Frank.
LanecH 的答案适用于 Rails 3+(包括 Rails 4 和 5):
或者创建一个可以重用的命名范围:
LanecH's answer adapted for Rails 3+ (including Rails 4 and 5):
Or create a named scope you can reuse:
现在,在 Rails 5.2 中,如果使用已接受的答案,您可能会收到警告。
替代方案可以依赖 Arel(它现在已合并到 Rails 中):
Now with Rails 5.2 you probably will get a warning if using the accepted answer.
An alternative could be relying on Arel (it's merged now into Rails):
您是否考虑过将列存储为 citext 类型?据我了解,它实际上只是内部化了对 lower() 的调用。之后这对您来说将是自动的。如果有时您需要区分大小写的搜索,但这可能不是最好的主意。
Have you considered storing your column as citext type? 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.
在 SQL 中,您可以使用 ORDER BY LOWER(columnname),不知道如何在 Ruby 中执行此操作。功能索引(也在 LOWER(columnname) 上)将有助于加快速度。
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.