Ruby-on-rails Rails Active Record find(:all, :order => ) 问题

Rails Active Record find(:all, :order => ) issue

提问by CodingWithoutComments

I seem to be unable to use the ActiveRecord::Base.find option :order for more than one column at a time.

我似乎无法使用 ActiveRecord::Base.find 选项 :order 一次多于一列。

For example, I have a "Show" model with date and attending columns.

例如，我有一个带有日期和参加列的“显示”模型。

If I run the following code:

如果我运行以下代码：

@shows = Show.find(:all, :order => "date")

I get the following results:

我得到以下结果：

[#<Show id: 7, date: "2009-04-18", attending: 2>, 
 #<Show id: 1, date: "2009-04-18", attending: 78>, 
 #<Show id: 2, date: "2009-04-19", attending: 91>, 
 #<Show id: 3, date: "2009-04-20", attending: 16>,
 #<Show id: 4, date: "2009-04-21", attending: 136>]

If I run the following code:

如果我运行以下代码：

@shows = Show.find(:all, :order => "attending DESC")

[#<Show id: 4, date: "2009-04-21", attending: 136>,
 #<Show id: 2, date: "2009-04-19", attending: 91>,
 #<Show id: 1, date: "2009-04-18", attending: 78>,
 #<Show id: 3, date: "2009-04-20", attending: 16>,
 #<Show id: 7, date: "2009-04-18", attending: 2>]

But, if I run:

但是，如果我运行：

@shows = Show.find(:all, :order => "date, attending DESC")

OR

或者

@shows = Show.find(:all, :order => "date, attending ASC")

OR

或者

@shows = Show.find(:all, :order => "date ASC, attending DESC")

I get the same results as only sorting by date:

我得到的结果与仅按日期排序相同：

 [#<Show id: 7, date: "2009-04-18", attending: 2>, 
 #<Show id: 1, date: "2009-04-18", attending: 78>, 
 #<Show id: 2, date: "2009-04-19", attending: 91>, 
 #<Show id: 3, date: "2009-04-20", attending: 16>,
 #<Show id: 4, date: "2009-04-21", attending: 136>]

Where as, I want to get these results:

哪里，我想得到这些结果：

[#<Show id: 1, date: "2009-04-18", attending: 78>,
#<Show id: 7, date: "2009-04-18", attending: 2>, 
 #<Show id: 2, date: "2009-04-19", attending: 91>, 
 #<Show id: 3, date: "2009-04-20", attending: 16>,
 #<Show id: 4, date: "2009-04-21", attending: 136>]

This is the query being generated from the logs:

这是从日志生成的查询：

[4;35;1mUser Load (0.6ms)[0m   [0mSELECT * FROM "users" WHERE ("users"."id" = 1) LIMIT 1[0m
[4;36;1mShow Load (3.0ms)[0m   [0;1mSELECT * FROM "shows" ORDER BY date ASC, attending DESC[0m
[4;35;1mUser Load (0.6ms)[0m   [0mSELECT * FROM "users" WHERE ("users"."id" = 1) [0m

Finally, here is my model:

最后，这是我的模型：

  create_table "shows", :force => true do |t|
    t.string   "headliner"
    t.string   "openers"
    t.string   "venue"
    t.date     "date"
    t.text     "description"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.decimal  "price"
    t.time     "showtime"
    t.integer  "attending",   :default => 0
    t.string   "time"
  end

What am I missing? What am I doing wrong?

我错过了什么？我究竟做错了什么？

UPDATE: Thanks for all your help, but it seems that all of you were stumped as much as I was. What solved the problem was actually switching databases. I switched from the default sqlite3 to mysql.

更新：感谢您的帮助，但似乎你们所有人都像我一样被难住了。解决问题的实际上是切换数据库。我从默认的 sqlite3 切换到 mysql。

采纳答案by brism

I notice that in your first example, the simple :order => "date", record 7is sorted before record 1. This order is also how you see the results in the multi-column sort, regardless of whether you sort by attending.

我注意到在你的第一个例子中，简单的:order => "date"，记录7排在记录1之前。此顺序也是您在多列排序中查看结果的方式，无论您是否按参加排序。

This would seem to make sense to me if the dates weren't exactly the same, and the date for 7is before the date for 1. Instead of finding that the dates are exactly equal then proceeding to sort by attending, the query finds that the dates are not equal and simply sorts by that like all the other records.

如果日期不完全相同，并且7的日期在1的日期之前，这对我来说似乎很有意义。查询发现日期不相等，而不是发现日期完全相等，然后继续按参与排序，而是像所有其他记录一样简单地按日期排序。

I see from browsing around that SQLite doesn't have a native understanding of DATE or DATETIME data types and instead gives users the choice of floating point numbers or text that they must parse themselves. Is it possible that the literal representation of the dates in the database are not exactly equal? Most people seem to need to use date functionsso that dates behave like you would expect. Perhaps there's a way to wrap your order by column with a date functionthat will give you something concrete to compare, like date(date) ASC, attending DESC. I'm not sure that syntax works, but it's an area to look at for solving your problem. Hope that helps.

我从浏览中看到 SQLite 没有对 DATE 或 DATETIME 数据类型的原生理解，而是让用户选择浮点数或他们必须自己解析的文本。数据库中日期的字面表示是否可能不完全相等？大多数人似乎需要使用日期函数，以便日期的行为符合您的预期。也许有一种方法可以用日期函数按列包装您的订单，该函数将为您提供一些具体的比较，例如date(date) ASC，参加 DESC。我不确定语法是否有效，但它是解决问题的一个领域。希望有帮助。

回答by thewillcole

Could be two things. First,

可能是两件事。第一的，

This code is deprecated:

此代码已弃用：

Model.find(:all, :order => ...)

should be:

应该：

Model.order(...).all

Find is no longer supported with the :all, :order, and many other options.

:all、:order 和许多其他选项不再支持查找。

Second,you might have had a default_scope that was enforcing some ordering before you called findon Show.

其次，你可能有这是执行一些命令你叫前default_scopefind上Show。

Hours of digging around on the internet led me to a few useful articles that explain the issue:

在互联网上挖掘了几个小时，让我找到了一些有用的文章来解释这个问题：

• http://m.onkey.org/active-record-query-interface
• http://www.simonecarletti.com/blog/2010/07/the-way-to-rails-3/

• http://m.onkey.org/active-record-query-interface
• http://www.simonecarletti.com/blog/2010/07/the-way-to-rails-3/

回答by oma

The problem is that dateis a reserved sqlite3 keyword. I had a similar problem with time, also a reserved keyword, which worked fine in PostgreSQL, but not in sqlite3. The solution is renaming the column.

问题是date是保留的 sqlite3 关键字。我有一个类似的问题time，也是一个保留关键字，它在 PostgreSQL 中运行良好，但在 sqlite3 中不起作用。解决方案是重命名列。

See this: Sqlite3 activerecord :order => "time DESC" doesn't sort

看到这个：Sqlite3 activerecord :order => "time DESC" 不排序

回答by Daniel Romero

I just ran into the same problem, but I manage to have my query working in SQLite like this:

我刚刚遇到了同样的问题，但我设法让我的查询在 SQLite 中工作，如下所示：

@shows = Show.order("datetime(date) ASC, attending DESC")

I hope this might help someone save some time

我希望这可以帮助某人节省一些时间

回答by gtd

Make sure to check the schema at the database level directly. I've gotten burned by this before, where, for example, a migration was initially written to create a :datetime column, and I ran it locally, then tweaked the migration to a :date before actually deploying. Thus everyone's database looks good except for mine, and the bugs are subtle.

确保直接在数据库级别检查架构。我以前被这个问题困扰过，例如，最初编写迁移是为了创建一个 :datetime 列，然后我在本地运行它，然后在实际部署之前将迁移调整为 :date。因此，除了我的之外，每个人的数据库看起来都不错，而且错误很微妙。

回答by Eimantas

isn't it only :order => 'column1 ASC, column2 DESC'?

不只是:order => 'column1 ASC, column2 DESC'吗？

回答by Eimantas

I understand why the Rails devs went with sqlite3 for an out-of-the-box implementation, but MySQL is so much more practical, IMHO. I realize it depends on what you are building your Rails app for, but most people are going to switch the default database.yml file from sqlite3 to MySQL.

我理解为什么 Rails 开发人员使用 sqlite3 进行开箱即用的实现，但 MySQL 更实用，恕我直言。我意识到这取决于您构建 Rails 应用程序的目的，但大多数人会将默认的 database.yml 文件从 sqlite3 切换到 MySQL。

Glad you resolved your issue.

很高兴您解决了您的问题。

回答by Jimmy Stenke

It is good that you've found your solution. But it is an interesting problem. I tried it out myself directly with sqlite3 (not going through rails) and did not get the same result, for me the order came out as expected.

很高兴您找到了您的解决方案。但这是一个有趣的问题。我自己直接用 sqlite3（不通过导轨）尝试了它，但没有得到相同的结果，对我来说，订单按预期出现。

What I suggest you to do if you want to continue digging in this problem is to start the sqlite3 command-line application and check the schema and the queries there:

如果您想继续解决这个问题，我建议您启动 sqlite3 命令行应用程序并检查架构和那里的查询：

This shows you the schema: .schema

这向您展示了架构： .schema

And then just run the select statement as it showed up in the log files: SELECT * FROM "shows" ORDER BY date ASC, attending DESC

然后只需运行日志文件中显示的 select 语句： SELECT * FROM "shows" ORDER BY date ASC, attending DESC

That way you see if:

这样你就可以看到：

1. The schema looks as you want it (that date is actually a date for instance)
2. That the date column actually contains a date, and not a timestamp (that is, that you don't have a time of the day that messes up the sort)

1. 架构看起来像你想要的（例如，该日期实际上是一个日期）
2. 日期列实际上包含一个日期，而不是一个时间戳（也就是说，您没有一天中的某个时间会弄乱排序）

回答by Maximus S

This might help too:

这也可能有帮助：

Post.order(created_at: :desc)

回答by Kamrul

I am using rails 6 and Model.all(:order 'columnName DESC') is not working. I have found the correct answer in OrderInRails

我正在使用 rails 6 并且 Model.all(:order 'columnName DESC') 不起作用。我在OrderInRails 中找到了正确答案

This is very simple.

这很简单。

@variable=Model.order('columnName DESC')