Implementing Two Level Aggregate in PostgreSQL using Sequelize Function for NodeJS App

Hello guys please bear with me here. I’m using PostgreSQL, Sequelize, Express, and NodeJS to create a backend. I’m wondering if these lines of raw query code can be implemented using Sequelize Model findAll function.

First of all, what I am trying to do here is to calculate the total score of these students. Here are some tables and their relations.

                 Student                               Level
| student_id |    name    | level_id |       | level_id | level_name |
|:----------:|:----------:|:--------:|       |:--------:|:----------:|
|      1     |    John    |     1    |   >   |     1    |   Rookie   |
|      2     |    Jane    |     2    |       |     2    |   Expert   |

                    v

              StudentQuiz
|   quiz_id  | student_id |   score  |
|:----------:|:----------:|:--------:|
|      1     |      1     |    40    |
|      1     |      1     |    100   |
|      2     |      1     |    80    |
|      1     |      2     |    100   |
|      2     |      2     |    100   |

If I run line of codes below.

   SELECT table2.student_id,
          s.canvasser_name,
          l.level_name,
          table2.total_score
   FROM (SELECT table1.student_id,
                sum(table1.max_score) total_score
         FROM (SELECT sq.student_id,
               max(sq.score) max_score
               FROM public.StudentQuiz sq
               GROUP BY sq.quiz_id, sq.student_id) table1
         GROUP BY table1.student_id) table2
   INNER JOIN public.Student s
           ON s.student_id = table2.student_id
   INNER JOIN public.Level l
           ON l.level_id = s.level_id
   ORDER BY table2.total_score DESC
   LIMIT 10;

I will get something like this.

| student_id |    name    |  level   |   total_score  |
|:----------:|:----------:|:--------:|:--------------:|
|      1     |    John    |  Rookie  |       180      |
|      2     |    Jane    |  Expert  |       200      |

Please note that I’m selecting the highest score if more than one quiz with the same id found.

Anyway, I want to implement it using sequelize built in function. What I’ve been trying to do is something like this.

    const result = await StudentQuiz.findAll({
      attributes: ['studentId', [sequelize.fn('sum', sequelize.fn('max', sequelize.col('score'))), 'totalPrice'], 'quizId'],
      group: 'studentId',
      include: [
        {
          model: Student,
          include: [{
            model: Level
          }],
        },
      ],
      offset: 0,
      limit: 10
    });

The code above throws an error message which is “aggregate function calls cannot be nested”.

Any kind of help will be appreciated. Thank you.

P.S. I know i can use sequelize.query() function to use the first code block shown, but that’s not the point.

Answer

I find the solution without using any raw query, though I need to get two tables, that is StudentQuiz and Student that coupled by Level. Here is my answer.

    // Finding maximum score and group it based on studentId and quizId
    const maxScoreList = await StudentQuiz.findAll({
      attributes: ['studentId', 'quizId', [sequelize.fn('max', sequelize.col('score')), 'maxScore']],
      group: ['studentId', 'quizId'],
      order: ['studentId', 'quizId'],
      raw: true
    });

    // Calculating total score for the same student for each quiz recorded
    const scoreArray = [maxScoreList.shift()];
    let index = 0;
    const unfilteredStudentId = maxScoreList.map((item) => {
      if (scoreArray[index].studentId !== item.studentId) {
        scoreArray.push(item);
        index += 1;
      }
      scoreArray[index].maxScore += item.maxScore;
      return item.studentId;
    });

    // Filtering studentId that show up more than one time
    const extractedStudentId = [...new Set(unfilteredStudentId)];
    
    // Finding student based on studentId inside extractedStudentId array
    const student = await Student.findAll({
      where: { id: extractedStudentId },
      attributes: ['id', 'canvasserId', 'canvasserName', 'canvasserImageUrl'],
      include: {
        model: Level,
        attributes: [['level_name', 'level'], ['icon_url', 'level_image_url']]
      },
      order: ['id'],
      raw: true,
      nest: true
    });

    // Combining total score list to student list
    const rankList = student.map((item, idx) => ({ ...item, totalScore: scoreArray[idx] }));

With this much complexity, I agree that using raw query by far the best approach for this case.