How to get all best session determined by the highest score for all user by theme

Question

Hi stackoverflow community ! To my back-end (node.js) on Sequelize environment, I want to get a scoreboard for a quiz game. The score calculations are done in the front, I just record the values in my database The scoreboard give many info, 'username', 'score', 'count_indicators', 'count_errors'. I want to get all best session by theme, determined by the highest scores for all user. But with my code, I get all score for all user in specific theme, is not that I want, I would like get uniquely one session per user which corresponds to the best score.

async getAllBestPlayByTheme (req, res) { 
      try {
        const theme_id = req.params.id;
        
        const theme = await Theme.findByPk(theme_id);

        if(!theme){
          return res.status(404).json({error: 'Theme not found'});
        }

        **const bestPlays = await Play.findAll({
          attributes: [[Sequelize.fn('MAX', Sequelize.col('score')), 'maxScore'], 'errors', 'count_indicators'],
          include: [{
            model: User,
            attributes: ['username'],
            as: 'player'
          }],
          where: {
            theme_id
          },
          group: ['player.id','Play.errors', 'Play.count_indicators'],
          order: [['maxScore', 'DESC']]
        })**
        

        if (bestPlays.length === 0) {
          return res.status(404).json({ message: "Score not found" });
        }
  
        res.json(bestPlays);
      } catch (error) {
        console.error('Error in bestPlays : ', error);
        res.status(500).json({ message: "Error to get score" });
      }
    },

I have no idea how to achieve my goal.

---

Finally I did something a little less complex, but I still need help to get to my final goal. With this new method I manage to recover all the "play" sessions of each user depending on the theme I choose. Now I would need help to find a way to make my search more specific, that is to say only retrieve one row (session) per user while keeping the one with the highest score.

const bestPlays = await Play.findAll({
      attributes: ['score', 'errors', 'count_indicators'],
      include: [
        {
          model: User,
          as: 'player',
          attributes: ['id', 'username'],
        },
      ],
      where: {
        theme_id
      },
      order: [['score', 'DESC']], // Ordonnez par score décroissant
    });

Okay finally I succeeded after a lot of testing at my expected result with this:

const bestPlays = await User.findAll({
           attributes:[
             'username',
             [
               Sequelize.literal('(SELECT MAX("score") FROM "play" WHERE "user_id" = "User".."id" AND "theme_id" = :theme_id)'),
               'maxScore'
             ],
             [Sequelize.literal('(SELECT "errors" FROM "play" WHERE "user_id" = "User".."id" AND "theme_id" = :theme_id ORDER BY "score" DESC LIMIT 1)'), 'errors'] ,
             [Sequelize.literal('(SELECT "count_indicators" FROM "play" WHERE "user_id" = "User"."id" AND "theme_id" = :theme_id ORDER BY "score" DESC LIMIT 1)'), 'count_indicators']
           ],
           where: Sequelize.literal(`EXISTS (SELECT 1 FROM "play" WHERE "user_id" = "User"."id" AND "theme_id" = ${theme_id})`),
           order: [[Sequelize.literal('"maxScore" DESC')]],
           replacements: { theme_id },
         });

However, I don't really like using subqueries in my sequelize ORM. Isn't there another way that only keeps the sequelize method and operator? My question may seem stupid, however I am still discovering and I don't know everything, I am aware of that. I'm waiting for feedback if you have cleaner ones.