javascript - sequlize order by 仅来自主查询

Question

如何仅从主查询中订购？这是我的例子。

     model
        .findAndCountAll({
            where: {
                status: 'Pending'
            },
            attributes: [
                'id',
                [Sequelize.literal('CAST((SELECT count(*) FROM "table1" WHERE "table1"."id" = "model"."id" ' +
                    ) AS INTEGER) '), 'table1Count']

            ],
            include: [
                {
                    model: model2,
                    as: 'model2'
                }
            ],
            limit: limit,
            offset: offset,
            order: [
                [Sequelize.literal('CASE WHEN  "table1Count" >= 1 THEN "table1Count" END'), 'DESC','NULLS LAST'],
                ['id', 'ASC']]
        })

生成的查询如下。在这里我得到'列“table1Count”不存在'。

 SELECT
  "model".*,
  "model2"."id"   AS "model2.id",
  "model2"."name" AS "model2.name"
FROM (SELECT
        "model"."id",
        CAST((SELECT count(*)
              FROM "table1"
              WHERE "table1"."id" = "model"."id" AS INTEGER) AS "table1Count"
      FROM "model" AS "model"
      WHERE "model"."status" = 'Pending'
      ORDER BY CASE WHEN "table1Count" >= 1 **how to avoid order by here**
        THEN "table1Count" END DESC NULLS LAST, "model"."id" ASC
      LIMIT '50'
      OFFSET '0') AS "model" LEFT OUTER JOIN
  "model2" AS "model2" ON "model"."id" = "model2"."modelId"
ORDER BY CASE WHEN "table1Count" >= 1
  THEN "table1Count" END DESC NULLS LAST, "model"."id" ASC;

我怎样才能只从主查询中订购？

预期查询如下..

 SELECT
  "model".*,
  "model2"."id"   AS "model2.id",
  "model2"."name" AS "model2.name"
FROM (SELECT
        "model"."id",
        CAST((SELECT count(*)
              FROM "table1"
              WHERE "table1"."id" = "model"."id" AS INTEGER) AS "table1Count"
      FROM "model" AS "model"
      WHERE "model"."status" = 'Pending'
      LIMIT '50'
      OFFSET '0') AS "model" LEFT OUTER JOIN
  "model2" AS "model2" ON "model"."id" = "model2"."modelId"
ORDER BY CASE WHEN "table1Count" >= 1
  THEN "table1Count" END DESC NULLS LAST, "model"."id" ASC

只需要来自主/外部查询的订单

标签： javascriptnode.jspostgresqlsequelize.js

Answer 1

我认为您所需要的只是separate : true单独运行您的查询

include: [
    {
        model: model2,
        as: 'model2' ,
        separate : true // <---- Magic is here
    }
],