Multi column ordering

[ghost]

I would like to have ordering by multiple columns, where each of them can be ascending and descending. Example: "ORDER BY date ASC, id DESC"

What about creating scope like this: order_by_date_asc_and_id_desc

Sadly, nesting orders with scopes is not possible in Rails 2.3.2, see here: https://rails.lighthouseapp.com/projects/8994/tickets/2253-named_scope-and-nested-order-clauses

So it should be created as ONE scope.

original LH ticket

This ticket has 0 attachment(s).

[ghost]

Multi column ordering

Yeah, that would be a nice feature and it’s probably something AR scopes should handle anyways. It looks like it will get applied though, I think they are just waiting on better tests.

by Ben Johnson

[ghost]

Multi column ordering

Actually my tests are showing that AR does support multi column ordering by chaining named scopes. Are you not seeing that?

by Ben Johnson

[ghost]

Multi column ordering

Hm, I tested the following with Rails 2.3.2:

@@@ Contact.scoped(:order => ’name DESC’)

=> SELECT * FROM contacts ORDER BY contacts.name DESC

Contact.scoped(:order => ’name DESC’).scoped(:order => ’id DESC’)

=> SELECT * FROM contacts ORDER BY contacts.name DESC

@@@

You will see that the second scope is ignored.

Are we talking about different things? ;-)

by Georg Ledermann

[ghost]

Multi column ordering

Yeah, my fault, it does chain them together when you pass order in an action method:

User.ascend_by_id.ascend_by_email.first(:order => "test")

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column ’test’ in ’order clause’: SELECT * FROM `users` ORDER BY test, users.id ASC LIMIT 1

But for me to make this work I would have to alter the default behavior of AR, which I don’t think is a good idea. I’m not sure what to do. I could do some tricky things in the Search object, but I want that to remain consistent with calling the named scopes directly.

by Ben Johnson

[ghost]

Multi column ordering

I agree with you. My current solution to have mutliple ordering is by adding some named scopes like this: @@@ ruby named_scope :ordered_by_name, :order => ’last_name ASC, first_name ASC’

Contact.search(:ordered_by_name => true) @@@

Somtimes I need to override the predefined searchlogic ordering scopes like this: @@@ ruby

Preserve insertion order for sorting by date

named_scope :descend_by_date, :order => ’date DESC, id DESC’ named_scope :ascend_by_date, :order => ’date ASC, id ASC’ @@@

For me, it’s ok to stay with this. We will see if ActiveRecord will merge multiple ordering scopes in the future.

by Georg Ledermann