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

Question

I can't seem to use the ActiveRecord :: Base.find parameter: order more than one column at a time.

For example, I have a "Show" model with a date and column visits.

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 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, how, I want to get the following 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 a query created from 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 your help, but it seems like you were all at a dead end as far as I was. What solved the problem was actually a database switch. I switched from default SQL3 to mysql.

Answer 1

I noticed that the first example is simple : order => "date" , record 7 is sorted before record 1 . This order also shows how you see the results in sorting multiple columns, regardless of whether you sort by visiting.

It seems to me that this makes sense if the dates do not match and the date 7 is before date 1 . Instead of finding that the dates s are exactly equal, and then sorting by visit , the query discovers that the dates are not equal and are simply sorted in the same way as all other records.

I see that SQLite does not have its own understanding of DATE or DATETIME data types and instead gives users a choice of floating point numbers or text that they need to parse on their own. Is it possible that the literal representation of dates in a database is not exactly the same? Most people seem to need to use date functions , so dates behave as you expected. Perhaps there is a way to wrap your order in a column with the date function , which will give you something specific to compare, for example, date (date) ASC, visiting DESC . I'm not sure if the syntax works, but this is the area to find a solution to your problem. Hope this helps.

Answer 2

There may be two things. Firstly,

This code is deprecated:

 Model.find(:all, :order => ...) 

it should be:

 Model.order(...).all 

Search is no longer supported with: all ,: order and many other options.

Secondly, perhaps you had a default_scope parameter that did some sort of order before you called find on Show .

Hours of digging on the Internet led me to several useful articles that explain the problem:

• http://m.onkey.org/active-record-query-interface
• http://www.simonecarletti.com/blog/2010/07/the-way-to-rails-3/

Answer 3

The problem is that the date is the sqlite3 reserved keyword . I had a similar issue over time, as well as a reserved keyword that worked fine in PostgreSQL but not in sqlite3. The solution renames the column.

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

Answer 4

I just ran into the same problem, but I managed to get my query to work in SQLite as follows:

 @shows = Show.order("datetime(date) ASC, attending DESC") 

Hope this helps someone save time

Answer 5

it is not only :order => 'column1 ASC, column2 DESC' ?

Answer 6

Be sure to check the schema at the database level directly. I was burned this earlier where, for example, the migration was originally written to create a column: datetime, and I ran it locally and then changed the transition to a date: before the actual deployment. This way all databases look good, except mine, and the errors are subtle.

Answer 7

I understand why Rails developers went with sqlite3 for a ready-made implementation, but MySQL is much more practical, IMHO. I understand that it depends on what you are building the Rails application for, but most people are going to switch the default database.yml file from sqlite3 to MySQL.

Glad to solve your problem.

Answer 8

Good thing you found your solution. But this is an interesting problem. I tried this directly with sqlite3 directly (without going through the rails) and did not get the same result, for me the order came out as expected.

What I suggest you do if you want to continue digging into this problem is to start the sqlite3 command line application and check the schema and queries there:

This shows you the diagram: .schema

And then just run the select statement as it appeared in the log files: SELECT * FROM "shows" ORDER BY date ASC, attending DESC

Thus you see that:

• The diagram looks the way you want (this date is actually a date, for example)
• That the date column actually contains the date, not the timestamp (i.e. that you don’t have the time of day that messed up the sort)

Answer 9

It may also help:

 Post.order(created_at: :desc)