Rails的活动记录查找（：所有，：为了=＆GT;）问题 [英] Rails Active Record find(:all, :order => ) issue

问题描述

我似乎无法使用的ActiveRecord :: Base.find选项：在时间顺序为多个列。

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

例如，我有一个秀模型，日期和出席列。

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

如果我运行下面的code：

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>]

如果我运行下面的code：

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")

或

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

或

@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?

更新：感谢您的帮助，但似乎大家都难倒不亚于我。有什么解决的问题实际上是交换数据库。我从默认的sqlite3切​​换到MySQL。

推荐答案

我注意到，您的第一个例子中，简单的：为了=>最新，记录的 7 被记录之前排序 1 。这个顺序也是你如何看待这个结果在多列排序，无论您是按参加。

I notice that in your first example, the simple :order => "date", record 7 is 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.

这似乎是有意义的我，如果日期是不完全一样的，日期为 7 与日期 1 之前。而是发现在日期是完全相等，则继续进行排序的参加，查询发现，该日期是不相等的，只是排序由像所有其他记录

This would seem to make sense to me if the dates weren't exactly the same, and the date for 7 is 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.

我从浏览周围的SQLite没有日期或日期时间数据类型的原生理解看，而是为用户提供了浮点数字或文字，他们必须分析自己的选择。是否有可能的日期在数据库中的文字再presentation并不完全等于？大多数人似乎需要使用日期函数使日期的行为，就像您期望。也许有一种方法用一个日期函数列来包装你的订单，会给你一些具体比较，例如日期（日期）ASC，参加DESC 。我不知道该语法的工作原理，但它是一个区域来看看解决你的问题。希望有所帮助。

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 functions so that dates behave like you would expect. Perhaps there's a way to wrap your order by column with a date function that 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.

这篇关于Rails的活动记录查找（：所有，：为了=＆GT;）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！