How i can select rows which are not present in other table?

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”

1 Like

I make this way:

// Retorna as classes que um estudante pode se vincular
async getClassesDisponiveisEstudante({ request }){
  const arrayClassesVinculadasEstudante = await Database
    .query()
    .select('class.id')
    .where('student_classes.student_id', request.params.student_id)
    .from('student_classes')
    .innerJoin('class', 'class.id', 'student_classes.class_id')
    .pluck('class.id')

  const classesDisponveis = await Classe.query()
                            .whereNotIn('id', arrayClassesVinculadasEstudante)
                            .fetch()

  return classesDisponveis 
}

probably have one way to do with one query, but for now it’s ok, if someone can do better, feel weelcome to post a answer

2 Likes