How i can show all classes that a student isn't stay in adonis?

Question

I have three tables:

students

id INT
name VARCHAR

class

id INT
description VARCHAR

student_classes

id INT
student_id (FOREIGN KEY of students.id)
class_id (FOREIGN KEY of class.id)

How i can return all classes of a student that is not in student_classes? I receive the student_id in request.params.student_id, i try something like:

async getAvailableClassesOfAStudent({ request }){
  const classes = await Database
    .query()
    .select('class.*')
    .from('class')
    .leftJoin('student_classes', 'class.id', 'student_classes.class_id')
    .whereNotIn('student_classes.student_id', request.params.student_id) 

  return classes 
}

I'm getting:

select "class".* from "class" left join "student_classes" on "class"."id" = "student_classes"."class_id" where "student_classes"."student_id" not in $1 - syntax error at or near "$1"

Answer 1

I think it might help you : How to select all records from one table that do not exist in another table?

Something like :

...

const classes = await Database
    .query()
    .select('class.*')
    .from('class')
    .leftJoin('student_classes', 'class.id', 'student_classes.class_id')
    .whereNull('student_classes.student_id') 

...