Sqlite3 activerecord: order => "time DESC" does not sort

Question

rails 2.3.4, sqlite3

I'm trying to do it

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

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

By chance, I found that he worked in Heroku (testing using the heroku console), which runs PostgreSQL. However, locally, using sqlite3, the new records will be sorted by the old, no matter what I set the time for. Like this (the output was deleted manually): the second entry is new:

Product ID: 2053939460, Time: "2010-04-24 23:00:04", created_at: "2010-04-24 23:00:05"

Product ID: 2053939532, Time: "2010-04-25 10:00:00", created_at: "2010-04-27 05:58:30"

Product ID: 2053939461, Time: "2010-04-25 00:00:04", created_at: "2010-04-25 00:00:04"

Product ID: 2053939463, Time: "2010-04-25 01:00:04", created_at: "2010-04-25 01:00:04"

It looks like it sorts by first key, id, not by time. Note that the query works fine on heroku, returning a properly ordered list! I like sqlite, it is so KISS, hope you can help me ...

Any suggestions?

---

UPDATE / SOLVE: time is the reserved sqlite3 keyword (date, by the way, too). Here's why :order => 'time DESC' works in PostgreSQL ( not a reserved keyword ), but not in sqlite3. The solution is to avoid using sqlite3 keywords as column names if you are ever going to sort them. Renaming solves the problem.

I tested the standard rails updated_at and created_at template, which works fine.

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

Answer 1

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 and it is better to get rid of the ambiguity first of all:

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

UPD : the problem seems to have appeared on SO:

Error finding active Rails entry (: all ,: order =>)