Sequelize查询中关联字段排序时Limit先执行的异常问题

问题现象 #

当按关联模型字段排序并设置Limit时，查询会先执行Limit再排序，而非先排序再限制。仅在按关联模型字段排序时出现此问题，按主模型字段排序或不设置Limit时均正常。

实际示例 #

现有数据：
Job A (Company 2), Job B (Company 1), Job C (Company 4), Job D (Company 3)

• 不设置Limit时按Company名称排序，结果为B, A, D, C，符合预期；
• 设置Limit为3时，实际返回B, A, C，但正确结果应为B, A, D，说明是先取了前3条A, B, C再排序。

关联关系 #

Company.hasMany(Job, { foreignKey: { name: 'companyId', allowNull: false} });
Job.belongsTo(Company, { foreignKey: { name: 'companyId', allowNull: false} });

模型定义 #

const Company = sequelize.define('company', {
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false,
    }
});

const Job = sequelize.define('job', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },
    // 其他无关字段省略
});

查询及配置代码 #

switch(req.query.orderField) {
    case 'company': {
        order = [ 
            'company', 'name', orderDirection
        ];
        break;
    }
    default:
        order = [ orderField, orderDirection ];
}

const options = {
    attributes: [
        'id',
        'title',   
        [Sequelize.fn('date_format', Sequelize.col('job.createdAt'), '%d/%m/%y'), 'jobDate'],

    ],
    order: [ order ],
    distinct: true,
    include: [ 
        {
            model: Company,
            attributes: ['name'],
        },  
    ]
};

// 注释掉这行后一切正常
if(req.query.limit) options.limit = parseInt(limit, 10);
if(req.query.index) options.offset = parseInt(index);

const results = await Job.findAndCountAll(options);

补充更新 #

之前为简化配置省略了另一个关联模型Applicant，实际Job的include中同时包含Company和Applicant（含嵌套模型Person）：

include: [ 
    {
        model: Company,
        attributes: ['name'],
    }, 
    {
        model: Applicant,
        attributes: [
            'id', 
            'cvUrl', 
            'personId',
            [Sequelize.fn('date_format', Sequelize.col('applicants.createdAt'), '%d/%m/%y'), 'createdAt'],
        ],
        include: [ 
            {
                model: Person,
                attributes: [ 'firstName', 'lastName', 'phone', 'email' ]
            } 
        ] 
    }
]

原本以为这不会有影响，但查看生成的SQL后发现存在子查询，简化伪查询：

SELECT job.* ... FROM(SELECT job.id ... FROM jobs AS job LIMIT 0 8) AS job ... JOINS HERE ORDER BY company.name ASC

实际生成的SQL：

Executing (default): SELECT `job`.*, `company`.`id` AS `company.id`, `company`.`name` AS `company.name`, `applicants`.`id` AS `applicants.id`, `applicants`.`cvUrl` AS `applicants.cvUrl`, `applicants`.`personId` AS `applicants.personId`, date_format(`applicants`.`createdAt`, '%d/%m/%y') AS `applicants.createdAt`, `applicants->application`.`id` AS `applicants.application.id`, `applicants->application`.`createdAt` AS `applicants.application.createdAt`, `applicants->application`.`updatedAt` AS `applicants.application.updatedAt`, `applicants->application`.`applicantId` AS `applicants.application.applicantId`, `applicants->application`.`jobId` AS `applicants.application.jobId`, `applicants->person`.`id` AS `applicants.person.id`, `applicants->person`.`firstName` AS `applicants.person.firstName`, `applicants->person`.`lastName` AS `applicants.person.lastName`, `applicants->person`.`phone` AS `applicants.person.phone`, `applicants->person`.`email` AS `applicants.person.email` FROM (SELECT `job`.`id`, `job`.`title`, `job`.`wage`, `job`.`location`, `job`.`description`, `job`.`jobType`, `job`.`position`, `job`.`pqe`, `job`.`featured`, `job`.`createdAt`, date_format(`job`.`createdAt`, '%d/%m/%y') AS `jobDate`, `job`.`companyId` FROM `jobs` AS `job` LIMIT 0, 8) AS `job` LEFT OUTER JOIN `companies` AS `company` ON `job`.`companyId` = `company`.`id` LEFT OUTER JOIN ( `applications` AS `applicants->application` INNER JOIN `applicants` AS `applicants` ON `applicants`.`id` = `applicants->application`.`applicantId`) ON `job`.`id` = `applicants->application`.`jobId` LEFT OUTER JOIN `people` AS `applicants->person` ON `applicants`.`personId` = `applicants->person`.`id` ORDER BY `company`.`name` ASC;

可以看到确实是先执行了Limit。但移除Applicant关联模型后，查询恢复正常：

Executing (default): SELECT `job`.`id`, `job`.`title`, `job`.`wage`, `job`.`location`, `job`.`description`, `job`.`jobType`, `job`.`position`, `job`.`pqe`, `job`.`featured`, `job`.`createdAt`, date_format(`job`.`createdAt`, '%d/%m/%y') AS `jobDate`, `job`.`companyId`, `company`.`id` AS `company.id`, `company`.`name` AS `company.name` FROM `jobs` AS `job` LEFT OUTER JOIN `companies` AS `company` ON `job`.`companyId` = `company`.`id` ORDER BY `company`.`name` ASC LIMIT 0, 8;

无法理解为什么移除Applicant关联模型会解决这个问题，寻求解答！

内容的提问来源于stack exchange，提问作者NickW