problem with ActiveRecord/Rails ordering of query data from MySQL -- no problem in SQLite

Question

I have the following two ActiveRecord queries in an application_helper.rb file:

@left_menu = Page.select('id, menu_name').where(:published => true, :left_menu => true).order("sort")

Also can be written as:

@left_menu = Page.select('id, menu_name').where(:published => true, :left_menu => true).order("'sort' ASC")

and:

@left_menu = Page.find(:all, :conditions => {:published => true, :left_menu => true}, :order => :sort)

Why does the first one fail to sort on the 'sort' column, while the second one does not? Both work in SQLite, but only the second one works in MySQL.

Any ideas?

MySQL must not have a default sort direction, which is understandable. — qpingu
– qpingu, Commented Feb 8, 2011 at 21:56

andrea · Accepted Answer · 2011-02-08 23:46:13Z

1

it's the quote in ther order params .
the query generated will be (similar to)

"SELECT id, title FROM `pages` WHERE (`pages`.`pub` = 1) ORDER BY 'sort' ASC"

its the char ' quote . It's wrong sql syntax , it's going to order by costant value not column value . sqlite allow it , mysql not.
try to simple use

Page.select('id, menu_name').where(:published => true, :left_menu => true).order("sort ASC")

without single quote in the order chain method parameters.

sorry for my english. have a nice day

answered Feb 8, 2011 at 23:46

andrea

3,5052 gold badges24 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1