最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin1浏览0评论

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!

Share Improve this question edited Nov 21, 2024 at 7:44 jarlh 44.8k8 gold badges50 silver badges67 bronze badges asked Nov 21, 2024 at 7:35 Mike MMike M 5,1326 gold badges42 silver badges62 bronze badges
Add a comment  | 

1 Answer 1

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.

发布评论

评论列表(0)

  1. 暂无评论