Use groupBy on related model with Lucid query

[sifloz]

Hello,
Before I start explaining the reason I’m asking for help, I’d like to thank you for taking the time to read my concerns.

I have a web app in which students (users) take different assessments (test/exams) that belong to a module where they are enrolled to. So in order to keep track of the assessments the students take I have a relationship in my database like so:

The students have no restrictions on taking as many assessments from the same module as they want. So for example, there can be three assessments for the same module and students. So there would be three student_assessments rows on my database related to this user & assessment.

I’m implementing a summary in which I bring the info from the assessments of each module and I want to get all the assessments a module has and all the student assessments. However, I only want the student_assessment row that has the greatest score (max) for each student.

I made a query like so in my controller:

const _modules = await Module.query()
                      .with('assessments.studentAssessment', (builder1) => {
                           builder1.with('user') //students
                                 //.groupBy('user_id') // this is commented
                                   .orderBy('score', 'desc')
                                   .orderBy('user_id')
                      })
                      .fetch()

So far, this query gives me almost all the info I want, however, I’d like to know how can I get the studentAssessment with the highest score for each student. As you can see, I tried using the aggregation function groupBy() and group by user, but it didn’t work. Regardless of using groupBy(), I wouldn’t know how to add a query or condition in which I can select the Max score for each user.

This query, currently retrieves me all the assignments a student answered, but I only want the one in which the user got the highest score, not all of them.

Notes: I wouldn’t like to use a loop and make another query on every studentAssignment to get the highest score for each user, so I want to know if there is any way to achieve this with Lucid models or query builder.

Thanks for the time to read and respond.

[McSneaky]

I don't think you can do it without subqueries :|
You should be able to make it with Lucid too, but it should look smth like this:

SELECT * FROM student_assessment WHERE user_id = 2 AND score = (SELECT MAX(score) FROM student_assessment WHERE user_id = 2);

Might need to use .whereRaw()