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   gt   date     I get the following results      lt Show id  7  date   2009-04-18   attending  2 gt       lt Show id  1  date   2009-04-18   attending  78 gt       lt Show id  2  date   2009-04-19   attending  91 gt       lt Show id  3  date   2009-04-20   attending  16 gt      lt Show id  4  date   2009-04-21   attending  136 gt     If I run the following code     shows   Show find  all   order   gt   attending DESC       lt Show id  4  date   2009-04-21   attending  136 gt      lt Show id  2  date   2009-04-19   attending  91 gt      lt Show id  1  date   2009-04-18   attending  78 gt      lt Show id  3  date   2009-04-20   attending  16 gt      lt Show id  7  date   2009-04-18   attending  2 gt     But  if I run    shows   Show find  all   order   gt   date  attending DESC     OR    shows   Show find  all   order   gt   date  attending ASC     OR    shows   Show find  all   order   gt   date ASC  attending DESC     I get the same results as only sorting by date       lt Show id  7  date   2009-04-18   attending  2 gt       lt Show id  1  date   2009-04-18   attending  78 gt       lt Show id  2  date   2009-04-19   attending  91 gt       lt Show id  3  date   2009-04-20   attending  16 gt      lt Show id  4  date   2009-04-21   attending  136 gt     Where as  I want to get these results      lt Show id  1  date   2009-04-18   attending  78 gt     lt Show id  7  date   2009-04-18   attending  2 gt       lt Show id  2  date   2009-04-19   attending  91 gt       lt Show id  3  date   2009-04-20   attending  16 gt      lt Show id  4  date   2009-04-21   attending  136 gt     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   gt  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   gt  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

User · Answer

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

User · Answer

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   gt   quot time DESC quot  doesn  39 t sort

User · Answer

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

User · Answer

This might help too   Post order created at   desc

User · Answer

isn t it only  order   gt   column1 ASC  column2 DESC

User · Answer

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

User · Answer

Could be two things  First   This code is deprecated   Model find  all   order   gt         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

User · Answer

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

User · Answer

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    The schema looks as you want it  that date is actually a date for instance  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

User · Answer

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

[ruby-on-rails] Rails Active Record find(:all, :order => ) issue

Examples related to ruby-on-rails

Examples related to ruby

Examples related to activerecord