Sequelize ORDER BY

Question

Hello I want to query a company and list all the users ordered by name This is what I have. Relationship works fine is just the ordering that´s not working. I don´t see the ORDER BY name when I debug the generated query.

const user = {
    model: models.User,
    as: "Users",
    order: [["name", "asc"]]
};

const options = {
    where: { id: 1 },
    include: [user]
};

models.Company.findOne(options)
    .then(company => console.log(company))
    .catch(error => console.log(error.message));

Answer 1

From the interface definition file, we can see the comment for IncludeOptions.order option.

  /**
   * Order include. Only available when setting `separate` to true.
   */
  order?: Order;

So, you need to set separate to true. I assume the relationship between Company and User is One-to-Many.

"sequelize": "^5.21.3". E.g.

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class Company extends Model {}
Company.init({}, { sequelize, modelName: 'companies' });

class User extends Model {}
User.init(
  {
    name: DataTypes.STRING,
  },
  { sequelize, modelName: 'users' },
);

Company.hasMany(User, { as: 'Users' });

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await Company.create(
      { Users: [{ name: 'tim' }, { name: 'elsa' }, { name: 'james' }] },
      { include: [{ model: User, as: 'Users' }] },
    );
    // test
    const company = await Company.findOne({
      where: { id: 1 },
      include: [
        {
          model: User,
          as: 'Users',
          separate: true,
          order: [['name', 'asc']],
        },
      ],
    });
    console.log(company.Users);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

execution result and generated SQL:

Executing (default): SELECT "companies"."id" FROM "companies" AS "companies" WHERE "companies"."id" = 1;
Executing (default): SELECT "id", "name", "companyId" FROM "users" AS "users" WHERE "users"."companyId" IN (1) ORDER BY "users"."name" ASC;
[ users {
    dataValues: { id: 2, name: 'elsa', companyId: 1 },
    _previousDataValues: { id: 2, name: 'elsa', companyId: 1 },
    _changed: {},
    _modelOptions:
     { timestamps: false,
       validate: {},
       freezeTableName: true,
       underscored: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: [Object],
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: {},
       indexes: [],
       name: [Object],
       omitNull: false,
       sequelize: [Sequelize],
       hooks: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       include: undefined,
       includeNames: undefined,
       includeMap: undefined,
       includeValidated: true,
       raw: true,
       attributes: undefined },
    isNewRecord: false },
  users {
    dataValues: { id: 3, name: 'james', companyId: 1 },
    _previousDataValues: { id: 3, name: 'james', companyId: 1 },
    _changed: {},
    _modelOptions:
     { timestamps: false,
       validate: {},
       freezeTableName: true,
       underscored: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: [Object],
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: {},
       indexes: [],
       name: [Object],
       omitNull: false,
       sequelize: [Sequelize],
       hooks: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       include: undefined,
       includeNames: undefined,
       includeMap: undefined,
       includeValidated: true,
       raw: true,
       attributes: undefined },
    isNewRecord: false },
  users {
    dataValues: { id: 1, name: 'tim', companyId: 1 },
    _previousDataValues: { id: 1, name: 'tim', companyId: 1 },
    _changed: {},
    _modelOptions:
     { timestamps: false,
       validate: {},
       freezeTableName: true,
       underscored: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: [Object],
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: {},
       indexes: [],
       name: [Object],
       omitNull: false,
       sequelize: [Sequelize],
       hooks: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       include: undefined,
       includeNames: undefined,
       includeMap: undefined,
       includeValidated: true,
       raw: true,
       attributes: undefined },
    isNewRecord: false } ]

data records in the database:

node-sequelize-examples=# select * from "users";
 id | name  | companyId
----+-------+-----------
  1 | tim   |         1
  2 | elsa  |         1
  3 | james |         1
(3 rows)

node-sequelize-examples=# select * from "companies";
 id
----
  1
(1 row)