#99 ✓moved_to_github
Georg Ledermann

Multi column ordering

Reported by Georg Ledermann | July 10th, 2009 @ 05:31 AM

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_sc...

So it should be created as ONE scope.

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson July 12th, 2009 @ 09:52 PM

    • State changed from “new” to “open”

    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.

  • Ben Johnson

    Ben Johnson July 15th, 2009 @ 07:49 PM

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

  • Georg Ledermann

    Georg Ledermann July 16th, 2009 @ 03:04 AM

    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? ;-)

  • Ben Johnson

    Ben Johnson July 19th, 2009 @ 01:25 PM

    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.

  • Georg Ledermann

    Georg Ledermann July 22nd, 2009 @ 04:01 AM

    I agree with you. My current solution to have mutliple ordering is by adding some named scopes like this:

    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:

      # 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.

  • Ben Johnson

    Ben Johnson August 7th, 2009 @ 06:30 PM

    • State changed from “open” to “moved_to_github”

    [state:"moved_to_github" bulk edit command]

  • trulyafrican

    trulyafrican March 17th, 2010 @ 02:38 PM

    Hey Ben,

    Am wondering whether you can order proxy columns for instance

    i have a student and person tables; something like

    Student class
    belongs_to :person

    Person class
    has_one :student

    i have columns (first_name and last_name on person)

    So i thought i would be able to do something like @students = Student.ascend_by_person_first_name...

    any ideas on this?

    Thanks

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket

Pages