CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
CodingWithoutComments
CodingWithoutComments

Reputation: 36118

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

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.

Upvotes: 76

Views: 156148

Answers (10)

Kamrul
Kamrul

Reputation: 21

I am using rails 6 and Model.all(:order 'columnName DESC') is not working. I have found the correct answer in OrderInRails

This is very simple. 

@variable=Model.order('columnName DESC')

Upvotes: 1

Maximus S
Maximus S

Reputation: 11095

This might help too:

Post.order(created_at: :desc)

Upvotes: 0

Daniel Romero
Daniel Romero

Reputation: 1588

I just ran into the same problem, but I manage to have my query working in SQLite like this:

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

I hope this might help someone save some time

Upvotes: 4

thewillcole
thewillcole

Reputation: 3045

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:

Upvotes: 46

oma
oma

Reputation: 40780

The problem is that date is a reserved sqlite3 keyword. I had a similar problem with time, also a reserved keyword, which worked fine in PostgreSQL, but not in sqlite3. The solution is renaming the column.

See this: Sqlite3 activerecord :order => "time DESC" doesn't sort

Upvotes: 14

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

It is good that you've found your solution. But it is an interesting problem. I tried it out myself directly with sqlite3 (not going through rails) and did not get the same result, for me the order came out as expected.

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

This shows you the schema: .schema

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

That way you see if:

  1. The schema looks as you want it (that date is actually a date for instance)
  2. That the date column actually contains a date, and not a timestamp (that is, that you don't have a time of the day that messes up the sort)

Upvotes: 2

user59278
user59278

Reputation:

I understand why the Rails devs went with sqlite3 for an out-of-the-box implementation, but MySQL is so much more practical, IMHO. I realize it depends on what you are building your Rails app for, but most people are going to switch the default database.yml file from sqlite3 to MySQL.

Glad you resolved your issue.

Upvotes: 2

gtd
gtd

Reputation: 17246

Make sure to check the schema at the database level directly. I've gotten burned by this before, where, for example, a migration was initially written to create a :datetime column, and I ran it locally, then tweaked the migration to a :date before actually deploying. Thus everyone's database looks good except for mine, and the bugs are subtle.

Upvotes: 3

brism
brism

Reputation: 1226

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.

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.

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.

Upvotes: 35

Eimantas
Eimantas

Reputation: 49354

isn't it only :order => 'column1 ASC, column2 DESC'?

Upvotes: 3

Related Questions