Sqlite3 activerecord:order => “时间DESC"指的是“时间DESC".不排序 [英] Sqlite3 activerecord :order => "time DESC" doesn't sort

问题描述

rails 2.3.4，sqlite3

rails 2.3.4, sqlite3

我正在尝试

Production.find(:all，:conditions => ["time>?"， start_time.utc]，:order => 时间DESC"， :limit => 100)

Production.find(:all, :conditions => ["time > ?", start_time.utc], :order => "time DESC", :limit => 100)

条件可以很好地工作，但是我在:order =>上遇到了问题.时间DESC .

The condition works perfectly, but I'm having problems with the :order => time DESC.

我偶然发现它在运行PostgreSQL的Heroku(使用heroku控制台进行测试)下工作.但是，无论我将 time 设置为什么，在本地使用sqlite3，新条目将按照旧条目进行排序.像这样(输出已被手动剥离):第二个条目是新的:

By chance, I discovered that it worked at Heroku (testing with heroku console), which runs PostgreSQL. However, locally, using sqlite3, new entries will be sorted after old ones, no matter what I set time to. Like this (output has been manually stripped): second entry is new:

生产ID:2053939460，时间:"2010-04-24 23:00:04"，created_at:"2010-04-24 23:00:05"

Production id: 2053939460, time: "2010-04-24 23:00:04", created_at: "2010-04-24 23:00:05"

生产ID:2053939532，时间:"2010-04-25 10:00:00"，created_at:"2010-04-27 05:58:30"

Production id: 2053939532, time: "2010-04-25 10:00:00", created_at: "2010-04-27 05:58:30"

生产ID:2053939461，时间:"2010-04-25 00:00:04"，created_at:"2010-04-25 00:00:04"

Production id: 2053939461, time: "2010-04-25 00:00:04", created_at: "2010-04-25 00:00:04"

生产ID:2053939463，时间:"2010-04-25 01:00:04"，created_at:"2010-04-25 01:00:04"

Production id: 2053939463, time: "2010-04-25 01:00:04", created_at: "2010-04-25 01:00:04"

似乎按主键 id 而不是时间排序.请注意，该查询在heroku上工作正常，返回正确排序的列表！我喜欢sqlite，所以KISS，希望您能帮助我...

Seems like it sorts on the primary key, id, not time. Note that the query works fine on heroku, returning a correctly ordered list! I like sqlite, it's so KISS, I hope you can help me...

有什么建议吗?

更新/已解决: time 是sqlite3的保留关键字( date 等也是).这就是:order => 'time DESC'在PostgreSQL中工作的原因( non -reserved关键字 )，但不在sqlite3中.解决方案是，如果要对它们进行排序，请避免将sqlite3关键字用作列名. 重命名可以解决问题.

UPDATE/SOLVED: time is a reserved sqlite3 keyword (date, amongst others, is too). This is why :order => 'time DESC' works in PostgreSQL (non-reserved keyword), but not in sqlite3. The solution is to avoid having sqlite3 keywords as column names if you ever intend to sort on them. Renaming solves the problem.

我已经使用标准导轨模式 updated_at 和 created_at 进行了测试，该模式非常有效.

I've tested with the standard rails pattern updated_at and created_at, which works perfectly.

我仍然喜欢在开发中使用sqlite3，它是如此简单，流畅，可以复制数据库并发送给您的合作伙伴.感谢@newtover！

I still prefer sqlite3 in development, it's so simple and smooth to work with, copy the database and send to your partner. Thanks to @newtover !

推荐答案

使用保留字而不带引号通常是个坏主意. time是SQLite中的内置函数，请尝试使用以下内容，从而更好地消除歧义:

It is usually a bad idea to use reserved words without surrounding quotes. time is a built-in function in SQLite, try using the following instead and better get rid of the ambiguity in the first place:

Production.find(:all,
                :conditions => ["`time` > ?", start_time.utc],
                :order => "`time` DESC",
                :limit => 100)

UPD :问题似乎出现在SO上:

UPD: The problem seems to have appeared on SO:

发布Active Record find(:all，:order =>)问题

这篇关于Sqlite3 activerecord:order => “时间DESC"指的是“时间DESC".不排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！