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

Question

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.

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.

Answer 1

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.

Second, you might have had a default_scope that was enforcing some ordering before you called find on 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/