How to select records that do not have relationships in a table in Node/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 can I return all table records class that has no relationship of a particular student in student_classes?

I get the value of student_id in request.params.student_id, I tried 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 taking it back:

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

The Adonis structure offers the possibility of establishing relationships at the model level.

Said what you have a relationship: many for many

Your student model should contain the following

class Student extends Model {
    classes () {
        return this.belongsToMany('App/Models/Class')
   }
}

Now your class model should contain the following

class Class extends Model   {
    students () {
        return this.belongsToMany('App/Models/Student')
   }
}

• Use the method called: "doesntHave()" to bring these lessons without a registered student

  const withOutClass = Class.query().doesntHave('students').fetch()
  

Links:

• method "doesn’t Have"
• lucid relationships

Answer 2

If anyone needs anything like that, I got it 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 
}