node.js - Sequelize PG : select with join, 订单，限制

Question

我在 Postgres 中使用 Sequelize 3.30.0。这是我要执行的查询:

SELECT works.title, artists.name, artists.relevance
FROM works
JOIN artist_works ON works.work_id = artist_works.work_id
JOIN artists ON artists.artist_id = artist_works.artist_id
WHERE artists.label LIKE '%mozart%'
ORDER BY artists.relevance DESC
LIMIT 50

如果我直接在 db 上执行它，它非常简单并且效果很好。但是，尝试对 Sequelize 执行相同操作似乎不起作用(没有结果)。这是 findAll 选项的样子:

Work.findAll({
   attributes : ['title'],
   include : [{
      attributes : [ 'name' ],
      model : Artist,
      where : { label : { $like : '%mozart%' } },
   }],
  limit : 50
})

Sequelize 生成的查询非常复杂:

SELECT "works".*, "artists"."artist_id" AS "artists.artistId", "artists"."name" AS "artists.name", "artists.artist_works"."artist_work_id" AS "artists.artist_works.artistWorkId", "artists.artist_works"."artist_id" AS "artists.artist_works.artistId", "artists.artist_works"."work_id" AS "artists.artist_works.workId", "artists.artist_works"."type" AS "artists.artist_works.type", "artists.artist_works"."artist_id" AS "artists.artist_works.artist_id", "artists.artist_works"."work_id" AS "artists.artist_works.work_id" 
FROM (
    SELECT "works"."work_id" AS "workId", "works"."title" 
    FROM "works" AS "works" 
    WHERE ( 
        SELECT "artist_works"."artist_work_id" 
        FROM "artist_works" AS "artist_works" 
        INNER JOIN "artists" AS "artist" ON "artist_works"."artist_id" = "artist"."artist_id" 
        WHERE ("works"."work_id" = "artist_works"."work_id") LIMIT 1 
    ) IS NOT NULL 
    LIMIT 50) AS "works" 
INNER JOIN (
   "artist_works" AS "artists.artist_works" 
      INNER JOIN "artists" AS "artists" ON "artists"."artist_id" = "artists.artist_works"."artist_id"
      ) ON "works"."workId" = "artists.artist_works"."work_id" AND "artists"."label" LIKE '%-mozart-%';

请注意，如果未设置限制，则查询有效。
此外，当我还尝试添加 order 选项时，查询会卡住。
想法？

Answer 1

存在相关问题 Sequelize with NodeJS can't join tables with limit ，解决方案似乎是将 subQuery: false 添加到 options 方法中的 findAll 对象。它可以防止创建与您相关的子查询。

我创建了相同的关联并添加了 subQuery: false 并且没有生成子查询，而且 LIMIT 和 ORDER 似乎可以工作。但是，如果您真的想使用 selectQuery 选项，我建议从 node_modules/sequelize/lib/dialects/abstract/query-generator.js 查看 subQuery 的源代码，因为文档中没有提到它，所以我不保证它会起作用