Answer the question
In order to leave comments, you need to log in
Query in sequelize. Group by doesn't work with include, how to fix?
In theory a query like this in sequelize should work
const data = await db.Event.findAll(
{
attributes: ['TypeEventId'],
include: [{
model: db.YearOfEventDefault,
attributes: ['year', [db.sequelize.fn('sum', db.sequelize.col('money')), 'total_money'], [db.sequelize.fn('sum', db.sequelize.col('count')), 'total_count']],
group: ['year'],
}],
group: ['TypeEventId', '"YearOfEventDefaults.year"'],
});
SELECT
"Event"."TypeEventId",
"YearOfEventDefaults"."year" AS "YearOfEventDefaults.year",
sum("money") AS "YearOfEventDefaults.total_money",
sum("count") AS "YearOfEventDefaults.total_count"
FROM "Events" AS "Event"
LEFT OUTER JOIN "YearOfEventDefaults" AS "YearOfEventDefaults"
ON "Event"."id" = "YearOfEventDefaults"."EventId"
AND ("YearOfEventDefaults"."deletedAt" IS NULL)
WHERE ("Event"."deletedAt" IS NULL)
GROUP BY "TypeEventId" , "YearOfEventDefaults.year";
SELECT
"Event"."id", // <------ вот это лишнее
"Event"."TypeEventId",
"YearOfEventDefaults"."id" AS "YearOfEventDefaults.id", // <------ вот это лишнее
"YearOfEventDefaults"."year" AS "YearOfEventDefaults.year",
sum("money") AS "YearOfEventDefaults.total_money",
sum("count") AS "YearOfEventDefaults.total_count"
FROM "Events" AS "Event"
LEFT OUTER JOIN "YearOfEventDefaults" AS "YearOfEventDefaults"
ON "Event"."id" = "YearOfEventDefaults"."EventId"
AND ("YearOfEventDefaults"."deletedAt" IS NULL)
WHERE ("Event"."deletedAt" IS NULL)
GROUP BY "TypeEventId", "YearOfEventDefaults"."year";
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question