D
D
Dmitry Izotov2021-03-25 18:03:43
SQL
Dmitry Izotov, 2021-03-25 18:03:43

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"'],
            });


in my opinion it should be equal to the following query
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";


but there are extra id's that I specifically wanted to avoid
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";


What should I write to get rid of them?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question