Select with joining many-to-many and count one-to-many-CodePudding

I have two queries and in one place I need them both. How could I modify the first one?

First (one-to-many):

    const server = await Server.findOne({
      where: { id },
      attributes: {
        include: [[Sequelize.fn('COUNT', Sequelize.col('users.id')), 'localUserCount']],
      },
      include: [{ model: User, attributes: [] }],
      group: ['Server.id'],
      raw: true,
    });

Second (many-to-many):

    const serverWithAdmins = await Server.findOne({
      where: { id },
      include: [{ model: AdminUser, as: 'adminUserList', through: { attributes: [] } }],
    });

I need something like this:

    const server = await Server.findOne({
      where: { id },
      attributes: {
        include: [[Sequelize.fn('COUNT', Sequelize.col('users.id')), 'userCount']],
      },
      include: [
        { model: User, attributes: [] },
        // New line
        { model: AdminUser, as: 'adminUserList', through: { attributes: [] } }
      ],
      group: ['Server.id'],
      raw: true,
    });

But with the last query I got errors like this:

"column \"adminUserList.id\" must appear in the GROUP BY clause or be used in an aggregate function"

I tied to add AdminUser attributes to group or add attributes, but I still can't get an array of AdminUser for Server like in the second query. Is it a way to use the aggregation function COUNT and join another relationship in one query?

CodePudding user response：

You need to turn Sequelize.fn into Sequelize.literal with a subquery and remove User from include option and group option:

const server = await Server.findOne({
      where: { id },
      attributes: {
        include: [
         [Sequelize.literal('(SELECT COUNT(*) FROM Users where Users.serverId=Server.id)'), 'userCount']
         ],
      },
      include: [
        { model: AdminUser, as: 'adminUserList', through: { attributes: [] } }
      ],
    });