代码之家 › 专栏 › 技术社区 › zishe

错误:在预加载期间,尝试使用Sequelize.cast或Sequelize.fn选择属性,但不为结果指定别名。

sequelize.js node.js javascript

0

zishe · 技术社区 · 6 年前

数据:一个 Office 有许多 OfficeLocations ,每个都有很多 Ratings . 我需要写一个查询,只获取 Offices 至少有一个 Rating . 我的疑问是:

let condition = {
    include: [{
        model: OfficeLocation.unscoped(),
        attributes: [
            '"Office"."id" as "Office.id"',
            '"OfficeLocations"."id" AS "OfficeLocation.id"'
        ],
        include: [
            {
                model: Rating.unscoped(),
                attributes: [
                    '*',
                    sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"'))
                ]
            }
        ],
        group: '"Office.id", "OfficeLocation.id"',
        having: sequelize.where(
            sequelize.fn('COUNT', sequelize.col('"OfficeLocations->Ratings"."id"')),
            '>',
            0
        )
    }]
}

Office.findAll(condition).then(data => {
    res.send(data);
}).catch(e => {
    console.log(e);
});

但我在控制台中有一个错误:

Error: Tried to select attributes using Sequelize.cast or Sequelize.fn without specifying an alias for the result, during eager loading. This means the attribute will not be added to the returned instance
at include.attributes.map.attr (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1307:17)
at Array.map (<anonymous>)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1287:52)
at Object.generateInclude (/Users/.../node_modules/sequelize/lib/dialects/abstract/query-generator.js:1355:39)

我也试过了

where: sequelize.literal('COUNT(DISTINCT(`OfficeLocations`.`Ratings`.`id`)) > 0'),

而不是属性/组/拥有,但它也不起作用。

谢谢。

更新

此SQL查询按我的需要工作:

sequelize.query(`
    SELECT
    "Office"."id" as "Office.id",
    "Office"."name",
    "Office"."website",
    "OfficeLocations"."id" AS "OfficeLocations.id",
    COUNT("OfficeLocations->Ratings"."id") as "RatingsCount"

    FROM "Companies" AS "Office"
    LEFT OUTER JOIN ( "OfficeLocations" AS "OfficeLocations"
        INNER JOIN "Ratings" AS "OfficeLocations->Ratings"
        ON "OfficeLocations"."id" = "OfficeLocations->Ratings"."OfficeLocationId"
    )
    ON "Office"."id" = "OfficeLocations"."OfficeId"

    GROUP BY "Office.id", "OfficeLocations.id"
    HAVING COUNT("OfficeLocations->Ratings"."id") > 0
`)

但我想取所有的数据。

1 回复 | 直到 6 年前

1

Faris 6 年前

请看 Model.findAll 句法 options 争论。

group 和 having 是的属性 选项 对象。
用于所选列的别名/expr( attributes 选项)使用数组: [expr, alias] .
你可以通过 attributes.include 和/或 attributes.exclude 数组。
你可以通过 include[].attributes 用于引用所包含模型的属性的选项。
你也可以用 include[].required 用于在内部联接和外部联接之间进行选择的选项。

你的情况:

let options = {
        include: [
            {
                model: OfficeLocation,
                required: false, //false for OUTER JOIN, but I think that you can use INNER JOIN
                attributes: [
                    "id", //this is OfficeLocation.id, see 4th item above. 
                    [Sequelize.fn("COUNT", Sequelize.col('`OfficeLocations->Ratings`.`id`')), "RatingsCount"]
                ],
                include: [
                    {
                        model: UserRating,
                        attributes: [],
                        required: true
                    }
                ]
            }
        ],
        group: [
            `Office.id`,
            `OfficeLocations.id`
        ],
        having: Sequelize.where(Sequelize.fn("COUNT", Sequelize.col('`OfficeLocations->Ratings`.`id`')), ">", 0)
    };

请注意,Sequelize生成的别名可能会更改,因此您应该将其更新为 Sequelize.col .