Rails 4 .order() gets mangled with JOINS

Question

I'm working on upgrading an existing Rails 3.2 application to 4.0. I've run into a brick wall, though.

I've got three models, Client, Site, and Contact. Sites are physical locations that belong to a client, and a client can have many sites. Contacts are people that belong to one or more sites. Thus, a client can have many contacts through sites.

Clients:

class Client < ActiveRecord::Base

  has_many :sites, -> { where(:sites => {:deleted => false}).order(:name => :asc) }, :dependent => :destroy
  has_many :contacts, -> { order(:lastname => :asc) }, :through => :sites

end

Sites:

class Site < ActiveRecord::Base

  belongs_to :client
  has_and_belongs_to_many :contacts

end

Contacts:

class Contact < ActiveRecord::Base

  has_and_belongs_to_many :sites

end

The problem is that when I use Client.find(1).contacts, I get a ActiveRecord::StatementInvalid exception:

Mysql2::Error: Unknown column 'contacts.name' in 'order clause': SELECT contacts.* FROM contacts INNER JOIN contacts_sites ON contacts.id = contacts_sites.contact_id INNER JOIN sites ON contacts_sites.site_id = sites.id WHERE sites.client_id = 5 AND sites.deleted = 0 ORDER BY contacts.lastname ASC, contacts.name ASC

There's the problem: I have no idea where the ORDER BY ... `contacts`.`name` ASC is coming from. The Contacts table does not have a name column, but Rails is trying to sort with it and I don't know where it's coming from or how to remove it. The ORDER BY `contacts`.`lastname` ASC is easy; it comes from the Client model.

These relationships have worked flawlessly in 3.2, but now throw this exception in 4.0.

UPDATE: It's been pointed out that the extra ORDER BY ... `contacts`.`name` ASC is coming from the first has_many in the Clients model. However, the intention there was to sort the Sites, not the Contacts. I tried changing it to .order('sites.name' => :asc) and SQL complained about there being no column named sites.sites.name. So, it appears that when using :through => with has_many, the order clause gets mangled.

I tried removing the .order() and using default_scope -> { order(:name => :asc) } in the Sites model, but got exactly the same error as originally reported.

Answer 1

The solution is simple. Instead of using .order(:column_name => :asc), I changed it to .order('column_name ASC') and the error went away, yielding the expected result. I originally had the latter, but was implementing the former as part of my Rails 3 -> 4 upgrade, and saw such convention in RailsCasts and other documentation.

This is also necessary when using .joins() and ordering by joined table columns, as I later found. This makes sense, because the association uses joins as well.

Answer 2

You must be tired, right there in your second line (Client model), you have order(:name=> :asc), it should be:

class Client < ActiveRecord::Base

  has_many :sites, -> { where(:sites => {:deleted => false}).order(:lastname => :asc) }, :dependent => :destroy
  has_many :contacts, -> { order(:lastname => :asc) }, :through => :sites

end