javascript - Sequelise 创建奇怪的查询

Question

我有 3 张桌子city, car, images

我还需要获取城市city/parent_id = 21的汽车以及汽车的图像。（使用 where 子句根据 sort_number 过滤图像）

我的原始查询是这样的：

SELECT
    `car`.`car_id`,
    `car`.`chassis_no`,
    `images`.`car_image_id` AS `images.car_image_id`,
    `images`.`directory` AS `images.directory`,
    `images`.`file_name` AS `images.file_name`
FROM `car` AS `car`
INNER JOIN `city` AS `city` ON
        `car`.`parent_id` = `city`.`id`
        AND `city`.`parent_id` = 21
INNER JOIN `images` AS `images` ON
    `car`.`car_id` = `images`.`car_id`
    AND `images`.`sort_number` = 1
limit 0, 5;

Sequelise 生成的查询是这样的：

explain SELECT
    `car` .*,
    `images`.`car_image_id` AS `images.car_image_id`,
    `images`.`directory` AS `images.directory`,
    `images`.`file_name` AS `images.file_name`
FROM
    (
    SELECT
        `car`.`car_id`,
        `car`.`chassis_no`
    FROM
        `car` AS `car`
    INNER JOIN `city` AS `city` ON
        `car`.`parent_id` = `city`.`id`
        AND `city`.`parent_id` = 21
    WHERE
        (
        SELECT
            `car_id`
        FROM
            `images` AS `images`
        WHERE
            (`images`.`sort_number` = 1
            AND `images`.`car_id` = `car`.`car_id`)
        LIMIT 1 ) IS NOT NULL
    LIMIT 0,
    5) AS `car`
INNER JOIN `images` AS `images` ON
    `car`.`car_id` = `images`.`car_id`
    AND `images`.`sort_number` = 1;

进行解释表明 sequelise 查询效率不高，而且在我看来确实很奇怪，而不是平面连接。两个查询的结果是一样的。

有我的关系：

//Relations
city.hasMany(car, { foreignKey: 'parent_id', sourceKey: 'id' })
car.belongsTo(city, {
  foreignKey: 'parent_id',
  sourceKey: 'id'
})

car.hasMany(image, { foreignKey: 'car_id', sourceKey: 'car_id' })
image.belongsTo(car, { foreignKey: 'car_id', sourceKey: 'car_id' })

查询代码如下：

return this.findAll({
      attributes: ['car_id', 'car_model'],
      include: [
        {
          model: db.city,
          required: true,
          attributes: [],
          where: { parent_id: 21 }
        },
        {
          model: db.image,
          required: true,
          attributes: ['image_id', 'file_name'],
          where: { sort_number: 1 }
        }
      ],
      offset: 0,
      limit: 5
    })

有没有办法让它像我上面的原始查询连接一样？我错过了什么？Sequelise 在生成更好的查询方面是否受到限制？我应该使用原始查询功能自己编写我的联接，这无济于事吗？

请指导，我是续集的新手。对不起，很长的帖子。

标签： javascriptnode.jssequelize.js