sequelize/sequelize — pull request #2496 by overlookmotel (2014-10-29 02:40:40+00:00)
Title: Fix for ambiguous column names in ORDER BY

This PR fixes a problem with ambiguous column names in ORDER BY clauses generated by `Model#find()`.

e.g.:

Task.find( { include: [ User ] }, order: [ [ 'id', 'ASC' ] ] } )

creates the following SQL:

SELECT *
FROM `Tasks` AS `Task`
INNER JOIN `Users` AS `User` ON `User`.`id` = `Task`.`UserId`
ORDER BY `id` ASC

MySQL and Postgres correctly interpret this as wanting to sort by `Task.id` but SQLite throws an error as the `ORDER BY id ASC` is ambiguous - should it order by `Task.id` or `User.id`?

This PR removes this ambiguity by altering the generated SQL to:

ORDER BY `Task`.`id` ASC

Some of the tests were not using the new `order` format that I implemented earlier this year ( `order: [ [ , , ] ]`, so I've amended them.

Another test (in all dialects) required the model to be passed to `QueryGenerator.quote()` as the third param, which is how `quote()` is always called from internal Sequelize code, so I amended the tests to do that too.

This will break BC for the following code:

Task.find( { include: [ User ] }, order: [ [ 'User.id', 'ASC' ] ] } )

but I think this was incorrect syntax anyway and was never documented as far as I know. It should be:

Task.find( { include: [ User ] }, order: [ [ User, 'id', 'ASC' ] ] } )

This newer way of using `order` has now been in the codebase for over 6 months.

What do you think?