How Does Group by Works in Sequelize

How does group by works in sequelize?

issue: https://github.com/sequelize/sequelize/issues/348

User.findAll({
 group: ['field']
})

i use sequelize@2.0.0-dev9

Group By with Sequelize

I solved it as follows:

  async index(req, res) {
    const { page = 1 } = req.query;

    const champions = await Champion.findAll({
      limit: 5,
      offset: (page - 1) * 5,
      order: [[fn('count', col('cupselection.selection.id')), 'DESC']],
      attributes: [[fn('count', col('cupselection.selection.id')), 'vezes']],
      group: ['cupselection.selection.id', 'cupselection.selection.logo.id'],
      raw: true,
      include: [
        {
          model: CupSelection,
          as: 'cupselection',
          attributes: [],
          include: [
            {
              model: Selection,
              as: 'selection',
              attributes: ['id', 'country'],
              include: [
                {
                  model: File,
                  as: 'logo',
                  attributes: ['id', 'path', 'url'],
                },
              ],
            },
          ],
        },
      ],
    });

    return res.json(champions);
  }

Using group by and joins in sequelize

This issue has been fixed on Sequelize 3.0.1, the primary key of the included models must be excluded with

attributes: []

and the aggregation must be done on the main model (infos in this github issue).

Thus for my use case, the code is the following

models.contracts.findAll({
    attributes: ['id', [models.sequelize.fn('sum', models.sequelize.col('payments.payment_amount')), 'total_cost']],
    include: [
    {
        model: models.payments,
        attributes: []
    }
    ],
    group: ['contracts.id']
})

Sequelize group by with association includes id

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.

---

Related Topics