CODEWITHSUNDEEP
group-bysequelize.js
Tikkes
Tikkes

Reputation: 4689

Sequelize group by with association includes id

So when requesting a group by from sequelize as follows:

return models.WorkingCalendar
            .findAll({
                attributes: [
                    'WorkingCalendar.PeriodId',
                    'WorkingCalendar.date',
                    'Period.name'
                ],
                include: [
                    {
                        model: models.Period,
                        attributes: []
                    }
                ],
                where: {
                    GetSudoId: currentGetsudo.id,
                    UnitPlantId: unitPlantId
                },
                group: ['WorkingCalendar.PeriodId',
                    'WorkingCalendar.date',
                    'Period.name'],
            });

Sequelize will run this query:

SELECT 
[WorkingCalendar].[id],
[WorkingCalendar].[PeriodId], 
[WorkingCalendar].[date], 
[Period].[name] 
FROM [WorkingCalendars] AS [WorkingCalendar] 
LEFT OUTER JOIN [Periods] AS [Period] ON [WorkingCalendar].[PeriodId] = [Period].[id] 
WHERE [WorkingCalendar].[GetSudoId] = 1 AND [WorkingCalendar].[UnitPlantId] = N'1' 
GROUP BY [WorkingCalendar].[PeriodId], [WorkingCalendar].[date], [Period].[name];

Yet I never asked for the WorkingCalender.id and I cannot seem to get rid of that. How do I make sure sequelize is not getting me this id from the workingCalendar?

I've already found that for associations the attributes should be an empty array and that works but not for the main object since I need only 3 columns.

Upvotes: 7

Views: 6172

Answers (2)

cubbuk
cubbuk

Reputation: 7920

Just pass raw flag to sequelize and it stops adding id field to attributes at least with sequelize 6:

const groups = ['WorkingCalendar.PeriodId', 'WorkingCalendar.date', 'Period.name']

return models.WorkingCalendar.findAll({
  attributes: groups,
  where: { GetSudoId: currentGetsudo.id, UnitPlantId: unitPlantId },
  include: [{ model: models.Period, attributes: [] }],
  group,
  raw: true,
})

Upvotes: 1

Tikkes
Tikkes

Reputation: 4689

So this is not possible. The ORM needs a relation towards the database and this is done through the id; making it impossible to get this without the id.

What you can do, however, is do a raw query with sequelize.

i.e.

return models.sequelize.query(
    `SELECT 
    [WorkingCalendar].[id],
    [WorkingCalendar].[PeriodId], 
    [WorkingCalendar].[date], 
    [Period].[name] 
    FROM [WorkingCalendars] AS [WorkingCalendar] 
    LEFT OUTER JOIN [Periods] AS [Period] ON [WorkingCalendar].[PeriodId] = [Period].[id] 
    WHERE [WorkingCalendar].[GetSudoId] = :getsudoId AND [WorkingCalendar].[UnitPlantId] = N'1' 
    GROUP BY [WorkingCalendar].[PeriodId], [WorkingCalendar].[date], [Period].[name]`,
    {
        replacements: { getsudoId: getsudoId },
        type: models.Sequelize.QueryTypes.SELECT
    }
)

Hope this helps anyone struggling too.

Upvotes: 1

Related Questions