Group By with Sequelize

Question

我有如下的类结构：

​

​

我尝试通过Sequelize对每个选择的Cup_Selections表中的多少个标题进行分组和计数，这些标题与下面输入的查询相同：

​

​

带Sequelize的My Node查询如下：

  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);
  }

但是向我显示了以下错误：

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

我该怎么解决呢？

Answer 1

我的解决方法如下：

  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);
  }