sequelize: Unable to order findAll results by nested associations

When eager-loading nested includes in findAll(), it is not possible to order by attributes of the nested associations.

e.g.:

var Company = sequelize.define('Company', {
    name: Sequelize.STRING
});

var Division = sequelize.define('Division', {
    name: Sequelize.STRING
});

var Department = sequelize.define('Department', {
    name: Sequelize.STRING
});

Company.hasMany(Division);
Division.hasMany(Department);

Company.findAll({include: [{model: Division, include: [Department]}], order: ['name', 'Divisions.name', 'Divisions.Departments.name']})

The findAll statement fails as it produces SQL:

ORDER BY `name`, `Divisions`.`name`, `Divisions`.`Departments`.`name`

rather than:

ORDER BY `name`, `Divisions`.`name`, `Divisions.Departments`.`name`

Of course, you can get around it by using {raw: ‘Divisions.Departments.name’}, but since the intention of the ORM is to distance the user from the underlying SQL, I feel it’d be better if using raw could be avoided.

Looks like this could be achieved by modifying QueryGenerator.quote() (or QueryGenerator.quoteIdentifiers() but I don’t know if that change would impact elsewhere in negative ways).

Apologies that I’m producing a lot of issues - just using (and loving) Sequelize a lot at the moment, and am coming across a few things as I go…

About this issue

Original URL
State: closed
Created 10 years ago
Reactions: 2
Comments: 57 (52 by maintainers)

Most upvoted comments

This should help: http://sequelize.readthedocs.org/en/latest/docs/models-usage/index.html#nested-eager-loading

JacopoDaeli on Dec 11, 2015

Back to the subject of the ordering by attributes of nested associations… I have two suggestions as to how it could be done.

If you have the following model definitions and find() statement:

// define tables
var Company = sequelize.define('Company', {name: Sequelize.STRING});
var Division = sequelize.define('Division', {name: Sequelize.STRING});
var Department = sequelize.define('Department', {name: Sequelize.STRING});

// define relationships
Company.hasMany(Division);
Division.belongsTo(Company);

Division.hasMany(Department);
Department.belongsTo(Division);

// get company
Company.find{include: [{model: Division, include: [Department]}]}.done(function(err, result) {
    console.log(err);
});

Two suggested options for the order clause if you want to order by the name of Department…

Option 1: order:[[Division, Department, 'name']] Option 2: order:['Division.Department.name']

If Division was aliased as DivisionAlias (i.e. include: [{model: Division, as: 'DivisionAlias'}]) then the order clauses become:

Option 1: order:[[{model: Division, as: 'DivisionAlias'}, Department, 'name']] Option 2: order:['DivisionAlias.Department.name']

Option 1 is a bit more rigorous in a way as it follows the same syntax as include, but it’s ugly. Option 2 I prefer.

One important point on option 2: the parts of the string refer to the model names NOT the table names. The implementation would have to create the SQL by translating the model names to table names by referring to includeMap.

Both solutions support schemas, as the details of the schema are held in the model and so the implementation can create the schema.table.column SQL from that. And everyone’s happy!

I’m happy to do the work to implement this, if we can agree on an API.

overlookmotel on Jan 16, 2014