I'm fairly new to using Sequelize and I'm having issues with findAndCountAll while using filters with eager loading. Following are the two models and their definitions.
const Campaign = sequelize.define(
"campaign",
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.BIGINT,
},
name: {
type: Sequelize.STRING(512),
},
advertiser_id: {
type: Sequelize.BIGINT,
},
},
{
timestamps: true,
createdAt: "created_at",
updatedAt: "modified_at",
});
Campaign.associate = function (model) {
Campaign.hasMany(model.performanceData, {
as: "performanceData",
foreignKey: "campaign_id",
constraints: false,
});
};
const PerformanceData = sequelize.define(
"performanceData",
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.BIGINT,
},
campaign_id: {
type: Sequelize.BIGINT,
},
clicks: {
type: Sequelize.BIGINT,
},
date: {
type: Sequelize.STRING(20),
},
},
{
tableName: "performance_data",
timestamps: true,
createdAt: "created_at",
updatedAt: "modified_at",
});
One campaign will have multiple performance data records based on date. I want to get all the campaigns by an advertiser (paginated) while eager loading the aggregate of performance data. So the performance data returned should be aggregate of a particular campaign.
Following is the query I'm using but it doesn't return the data:
const campaigns = await Campaign.findAndCountAll({
where: {
advertiser_id: reqObject.advertiser_id,
},
include: {
as: "performanceData",
model: PerformanceData,
attributes: [],
},
attributes: {
include: [
[
db.Sequelize.fn("SUM", db.Sequelize.col("performanceData.clicks")),
"clicks",
],
],
},
group: ["campaign.id"],
offset: reqObject.page,
limit: reqObject.size});
Can anyone please help with this?
