mysql - 在 Node Sequelize 中出现以下错误 - 'resortReview.rating' 中的未知列 'field list'

Question

尝试执行以下查询

> Resort.findAndCountAll(
>         {
>             where: { status: "active", name: { [Sequelize.Op.like]: '%' + _search_like + '%' } },
>             attributes: [
>                 "id",
>                 "name",
>                 "email",
>                 "mobileNumber",
>                 "averagePrice",
>                 "description",
>                 "rating",
>                 "location",
>                 "city",
>                 "country",
>                 [Sequelize.fn('AVG', Sequelize.col('resortReview.rating')), 'total'],
>                 [models.sequelize.literal(`Concat('` + imageHelper.SERVER_IMAGE_ROOT_URL + `',image)`), 'image']],
>             include: [{
>                 model: models.resortReview,
>                 required : false,
>                 attributes: []
>             }],
>             limit: limit,
>             offset: offset,
>             group: ["resort.id"],
>             order: [
>                 [sort, order],
>             ]
>         }
>     )

请找到关联，

db.resortReview.belongsTo(db.resort, {onDelete : 'CASCADE'});
db.resort.hasMany(db.resortReview, {foreignKey : 'resortId'});

但出现以下错误

Unhandled rejection SequelizeDatabaseError: Unknown column 'resortReview.rating' in 'field list' .

生成的查询:-

SELECT `resort`.* 
FROM   (SELECT `resort`.`id`, 
               `resort`.`name`, 
               `resort`.`email`, 
               `resort`.`mobilenumber`, 
               `resort`.`averageprice`, 
               `resort`.`description`, 
               `resort`.`rating`, 
               `resort`.`location`, 
               `resort`.`city`, 
               `resort`.`country`, 
               Concat('http://hamsontech.com:9093/images/', image) AS `image`, 
               Avg(`resortreviews`.`rating`)                       AS `total` 
        FROM   `resorts` AS `resort` 
        WHERE  `resort`.`status` = 'active' 
               AND `resort`.`name` LIKE '%%' 
        GROUP  BY `resort`.`id` 
        ORDER  BY `resort`.`name` ASC 
        LIMIT  0, 10) AS `resort` 
       LEFT OUTER JOIN `resortreviews` AS `resortReviews` 
                    ON `resort`.`id` = `resortreviews`.`resortid` 
ORDER  BY `resort`.`name` ASC; 

不知道我错过了什么？

Answer 1

我认为您的问题是您没有在连接表的属性列表中包含评级。尝试一下:

Resort.findAndCountAll({
    where: { status: "active", name: { [Sequelize.Op.like]: '%' + _search_like + '%' } },
    attributes: [
        "id",
        "name",
        "email",
        "mobileNumber",
        "averagePrice",
        "description",
        "location",
        "city",
        "country",
        [Sequelize.fn('AVG', Sequelize.col('resortReview.rating')), 'total'],
        [models.sequelize.literal(`Concat('` + imageHelper.SERVER_IMAGE_ROOT_URL + `',image)`), 'image']
    ],
    include: [{
        model: models.resortReview,
        required : false,
        attributes: ["rating"]
    }],
    limit: limit,
    offset: offset,
    group: ["resort.id"],
    order: [
        [sort, order],
    ]
})

如果您启用 Sequelize logging 选项并查看它生成的原始查询，也会有所帮助。这应该可以帮助您追踪问题。