用限制和偏移参数序列化不正确的查询

Question

我有下一个模型:

const Picture = sequelize.define<IPictureInstance>('picture', {
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
img: { type: DataTypes.STRING, allowNull: false },
mainTitle: { type: DataTypes.STRING, allowNull: false },
description: { type: DataTypes.TEXT }
});
const PictureType = sequelize.define('pictureType', {
id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
name: { type: DataTypes.STRING }
});

和它们的联系:

PictureType.hasMany(Picture);
Picture.belongsTo(PictureType);

我试图得到一个列表op pictureTypes与数量的图片，为此，我创建了下一个查询:

const pictureTypes = await models.PictureType.findAll({
attributes: {
include: [
[Sequelize.literal(`(SELECT COUNT(*) FROM pictures AS picture WHERE picture.pictureTypeId = pictureType.id)`), "picturesAmount"]
]
},
limit: limitValue,
offset: offsetValue
});
return pictureTypes;
};

但结果我得到了下一条消息:"столбец图片。图片类型не существует"翻译过来就是:"栏图"。图片类型不存在"。

问题是什么?我很感激你的帮助。

Answer 1

根据您的DBMS，您需要用特殊字符或混合情况将列包装为"(通常是PostgreSQL)或反引号(MySQL)，以便DBMS将解释此列名而不是试图将其转换为小写。

const pictureTypes = await models.PictureType.findAll({
attributes: {
include: [
[Sequelize.literal(`(SELECT COUNT(*) FROM pictures AS picture WHERE picture."pictureTypeId" = pictureType.id)`), "picturesAmount"]
]
},
limit: limitValue,
offset: offsetValue
});
return pictureTypes;
};