How to user "smart query conditions" in MikroORM in many-to-many relations #6250

Context

I have two entities:

@Entity({ tableName: 'students' })
export class StudentEntity {
  @Property()
  firstName: string;

  @Property()
  lastName: string;

  @ManyToMany({
  entity: () => TeacherEntity,
    pivotTable: 'students_to_teachers',
    joinColumn: 'student_id',
    inverseJoinColumn: ;teacher_id'
  })
  teachers = new Collection<TeacherEntity>(this);
}

and ...

@Entity({ tableName: 'teachers' })
export class TeacherEntity {
   @OneToOne(() => UserEntity, {
    owner: true,
    fieldName: 'user_id',
  })
  user: UserEntity;

  @ManyToMany({
    entity: () => StudentEntity,
    pivotTable: 'students_to_teachers',
    joinColumn: 'teacher_id',
    mappedBy: (s: StudentEntity) => s.teachers
  })
  students = new Collection<StudentEntity>(this);
}

Task

Given a userId for a UserEntity of a TeacherEntity, I can take all the students who are connected to this teacher via

const students = await this.studentsRepo.find(
  {
    teachers: {
      user: {
        id: userId
      }
    }
  }
);

Problem

Now I want the opposite. I want to take all students who are not connected with this teacher. Instinctively, I did something like

const students = await this.studentsRepo.find(
  {
    teachers: {
      user: {
        $ne: {
          id: userId
        }
      }
    }
  }
);

but it did not work.

Please advise!

Solution

You can use collection operators for this:

const students = await this.studentsRepo.find({
  teachers: {
    $none: {
      user: userId // no need to query by `user.id` explicitly
    }
  }
});

https://mikro-orm.io/docs/query-conditions#collection

Also what you tried was wrong, $ne represents the operator, you need to apply that on the lowest level, so user: { id: { $ne: 123 } } instead of user: { $ne: { id: 123 } } (but again, no need for the explicit id query here at all). But this would probably not do what you want, you need subqueries most likely (which is what the collection operators are doing).

And next time you don't have to ask the same thing on GH and SO within 2 minutes, I am subscribed to both.