Group By with Sequelize

Question

I have the following class structure:

I’m trying to group and count Sequelize how many titles each selection had from the table Cup_Selections same as the query typed below:

My Node consultation with Sequelize is as follows:

  async index(req, res) {
    const champions = await Champion.findAll({
      attributes: ['id', 'cupselection_id'],
      include: [
        {
          group: ['selection_id', 'champion.id'],
          raw: true,
          model: CupSelection,
          as: 'cupselection',
          attributes: [
            'id',
            'cup_id',
            [fn('count', col('selection_id')), 'vezes'],
          ],
          include: [
            {
              model: Selection,
              as: 'selection',
              attributes: ['id', 'country'],
            },
          ],
        },
      ],
    });

    return res.json(champions);
  }

But the following error is displayed to me:

(node:28230) UnhandledPromiseRejectionWarning: SequelizeDatabaseError: column "Champion.id" must appear in the GROUP BY clause or be used in an aggregate function

How can I solve ?

Answer 1

I decided as follows:

  async index(req, res) {
    // Recebendo os Query Parameters
    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);
  }