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

admin管理员组

文章数量:1332377

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!

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!

• postgresql
• orm
• mikro-orm

Share Improve this question Follow edited Nov 21, 2024 at 7:44 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Nov 21, 2024 at 7:35 Mike MMike M 5,13266 gold badges4242 silver badges6262 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.

本文标签：