Rails Active Record find(:all, :order =>) 问题 [英] 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.

如果我运行以下代码:

@shows = Show.find(:all, :order => "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>]

如果我运行以下代码:

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

但是，如果我跑:

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

我想得到这些结果:

[#<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

最后，这是我的模型:

  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.

推荐答案

我注意到在你的第一个例子中，简单的 :order => "date"，记录 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 没有对 DATE 或 DATETIME 数据类型的原生理解，而是让用户选择浮点数或他们必须自己解析的文本.数据库中日期的字面表示是否可能不完全相等?大多数人似乎需要使用日期函数，以便日期的行为与您预期的一样.也许有一种方法可以使用 日期函数按列包装您的订单会给你一些具体的比较，比如日期(日期)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 Active Record find(:all, :order =>) 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！