mysql - Sequelize : select models with a given number of matching children

Question

在我的架构中，User hasMany GameSession，GameSession hasMany Game。

对于特定用户，我正在尝试选择最近的游戏 session ，其中包含三场比赛，每场比赛都有非空分数。

This comment看起来它几乎完全符合我的要求，但它对我不起作用。它建议此代码:

ModelA.findAll({
  include: [ModelB],
  having: 'count(ModelB.id) > 0'
});

我认为上面的评论可能已经过时了，因为除非我将 having 子句包装在一个数组中，否则我会得到 Error: where: "raw query"has been removed, please use其中 [“原始查询”，[替换]]。

所以我有这个 JS:

user.getGameSessions({
  include: [
    {
      model: models.Game,
      where: {
        score: {
          $ne: null
        }
      }
    }
  ],
  having: ['count(Games.id) = 3'],
  order: [['updatedAt', 'DESC']],
  limit: 1
}).then(function(gameSessions) {
  res.json(gameSessions);
});

这是它正在进行的查询:

SELECT
  `GameSession`.*,
  `Games`.`id` AS `Games.id`,
  ...
  FROM (
    SELECT
      `GameSession`.`id`,
      ...
      `GameSession`.`UserId`
    FROM `GameSessions` AS `GameSession`
    WHERE `GameSession`.`UserId` = 33 AND (
      SELECT `GameSessionId`
      FROM `Games` AS `Game`
      WHERE (
        `Game`.`GameSessionId` = `GameSession`.`id`
        AND `Game`.`score` IS NOT NULL
      )
      LIMIT 1
    ) IS NOT NULL
    HAVING count(Games.id) = 3
    ORDER BY `GameSession`.`updatedAt` DESC
    LIMIT 1
  ) AS `GameSession`
  INNER JOIN `Games` AS `Games`
  ON `GameSession`.`id` = `Games`.`GameSessionId`
  AND `Games`.`score` IS NOT NULL
  ORDER BY `GameSession`.`updatedAt` DESC;

但是运行它会出现以下错误:SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'Games.id' in 'having clause'。我还尝试在 having 子句中使用单数 Game.id，因为我并不总是确定何时使用复数以及何时使用单数，而且我得到类似的错误。

我做错了什么？我怎样才能使它工作？

Answer 1

Is it possible to limit an include?

Currently no. I think there's a feature request open for it (although i couldn't find it with a cursory search. It's not trivial to implement since it requires a join on a subquery.