为什么我的Rails应用程序中的字母顺序会中断？

I have a very simple rails page showing "projects," which have a title, year, and file associated with them. Here is my controller:

我有一个非常简单的rails页面显示“项目”，它们有一个标题，年份和与之关联的文件。这是我的控制器：

# GET /projects
# GET /projects.json
def index
  @projects = Project.all.order(:title)
end

And here is index.html.erb:

这是index.html.erb：

<ul>
  <% @projects.each do |project| %>
    <li><a href="<%= project.document.url %>" target="_blank"><em><%= project.title %></em>, <%= project.year %></a>
      <% if logged_in? %>
       <br><%= link_to 'Edit', edit_project_path(project) %> / <%= link_to 'Destroy', project, method: :delete, data: { confirm: 'Are you sure?' } %>
      <% end %>
    </li>
  <% end %>
  <% if logged_in? %>
  <li><%= link_to 'New Project', new_project_path %><p id="notice"><%= notice %></p></li>
<% end %>
</ul>

Using psql (10.3, server 9.6.2) on Heroku

在Heroku上使用psql（10.3，服务器9.6.2）

All of the results are in perfect alphabetical order by title, except for the ones called "Spring House," "Springg House, "Springgg House," etc. Other titles using this pattern sort correctly, but for some reason, the "Spring" ones don't.

除了名为“Spring House”，“Springg House”，Springgg House，“等等之外，所有结果都按照完全按字母顺序排列。其他使用此模式的标题排序正确，但出于某种原因，”春天“没有。

Do you have any advice about where I should look in the app to fix this?

你有什么建议我应该在应用程序中查找以解决这个问题吗？

Thank you for any help!

感谢您的任何帮助！

1 个解决方案

#1

This is a "feature" of how Postgresql handles sorting - and to make matters worse, it varies from database to database, and even from platform to platform.

这是Postgresql如何处理排序的“特性” - 更糟糕的是，它因数据库而异，甚至从平台到平台也各不相同。

When you write Project.all.order(:title), Rails generates SQL (as you correctly figured out in a comment above) like this:

当您编写Project.all.order（：title）时，Rails会生成SQL（正如您在上面的注释中正确指出的那样），如下所示：

SELECT "projects".* FROM "projects" ORDER BY "projects"."title" ASC

This leaves Postgresql, or whatever other database you're using, to determine the order. Postgresql uses collations to determine order, which are locale-dependant. You can see what collations your databases are using by executing the \l command in psql. On my machines, for example, my databases default to en_US.UTF-8.

这使得Postgresql或您正在使用的任何其他数据库都可以确定订单。 Postgresql使用排序规则来确定顺序，这取决于区域设置。您可以通过在psql中执行\ l命令来查看数据库正在使用的归类。例如，在我的机器上，我的数据库默认为en_US.UTF-8。

Here's where it gets tricky. I created a table in postgres as follows:

这是它变得棘手的地方。我在postgres中创建了一个表，如下所示：

CREATE TABLE sorttest (name text);
INSERT INTO sorttest VALUES ('Spring House');
INSERT INTO sorttest VALUES ('Springg House');
INSERT INTO sorttest VALUES ('Springgg House');
SELECT * FROM sorttest ORDER BY name ASC;

On my Mac (Mac OS 10.13.3), it returns

在我的Mac（Mac OS 10.13.3）上，它返回

name      
----------------
Spring House
Springg House
Springgg House

However, on my Debian machine, it returns

但是，在我的Debian机器上，它返回

name      
----------------
Springgg House
Springg House
Spring House

As best as I can tell, the Mac is actually "doing it wrong", although it's the result you want. My Debian box, and your Heroku dyno, are sorting according to the UTF-8 spec: ignoring whitespace and capitalization, "springgghouse" should come before "springhouse".

据我所知，Mac实际上是“做错了”，尽管这是你想要的结果。我的Debian盒子和你的Heroku dyno按照UTF-8规范排序：忽略空白和大写，“springgghouse”应该在“springhouse”之前出现。

If you want to sort using a different collation (say, the "C" or the "POSIX" collation), you need to use a SQL command like this:

如果要使用其他排序规则（例如，“C”或“POSIX”排序规则）进行排序，则需要使用如下SQL命令：

SELECT "projects".* FROM "projects" ORDER BY "projects"."title" COLLATE "C" ASC

Fortunately, you can get there with ActiveRecord:

幸运的是，你可以使用ActiveRecord到达那里：

Project.all.order('title COLLATE "C"')

However, please note that this will make capitalization matter in your sort order - the "C" collation compares ASCII byte values, so capital letters will sort before lower case, eg:

但请注意，这将使您的排序顺序大写 - “C”排序规则比较ASCII字节值，因此大写字母将在小写字母之前排序，例如：

SELECT * FROM sorttest ORDER BY name COLLATE "C" ASC;

name      
----------------
Spring House
SpringGg House
Springg House

#1