Rails 4.order () gets confused with JOINS

Question

I am working on upgrading an existing Rails 3.2 application to 4.0. However, I ran into a brick wall.

I have three models: client, site and contact. Sites are physical locations owned by a client, and a client can have many sites. Contacts are people who belong to one or more sites. Thus, the client can have many contacts through sites.

Customers:

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 an ActiveRecord::StatementInvalid exception:

Mysql2 :: Error: Unknown column "contacts.name" in the "order" section: 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 is a problem: I have no idea where ORDER BY ... `contacts`.`name` ASC comes from ORDER BY ... `contacts`.`name` ASC . There is no name column in the Contacts table, but Rails tries to sort it, and I don’t know where it came from or how to delete it. ORDER BY `contacts`.`lastname` ASC easy; It comes from the customer model.

This relationship worked flawlessly in 3.2, but now throw this exception in 4.0.

UPDATE: It was pointed out that the additional ORDER BY ... `contacts`.`name` ASC comes from the first has_many in the Client model. However, the goal was to sort the Sites, not the Contacts. I tried changing it to .order('sites.name' => :asc) , and SQL complained about the lack of a column named sites.sites.name . So it seems that when using :through => with has_many sentence of the order gets distorted.

I tried to remove .order() and use 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 .order('column_name ASC') away giving the expected result. I was originally the last, but implemented the first as part of my Rails 3 → 4 update and saw such an agreement in RailsCasts and other documentation.

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

Answer 2

You must be tired right 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