mikro-orm: Many to Many filter doesnt work in my case

One to many filter works for me see below query. For ManyToMany the query doesnt recognize the filter i assigned. What am i doing wrong?

employee.ts

 @Field(() => [Benefit], { nullable: true })
  @ManyToMany(() => Benefit)
  public benefits?: Collection<Benefit> = new Collection<Benefit>(this);

  @Field(() => [DayOff], { nullable: true })
  @OneToMany({
    entity: () => DayOff,
    mappedBy: 'employee',
    orphanRemoval: true,
  })
  public dayOffs = new Collection<DayOff>(this);

dayoff.ts

@Filter({
  name: FILTERS.CURRENT_DAY_OFF,
  cond: (args) => ({
    dateTo: { $eq: null },
  }),
  default: false,
}) 
export class DayOff extends Base<DayOff> {

  @Field({ nullable: true })
  @Property({ nullable: true })
  public dateTo?: Date;
}

benefit.ts

@Filter({
  name: FILTERS.IS_ACTIVE,
  cond: (args) => ({
    benefitStatus: { $eq: STATUS.ACTIVE },
  }),
  default: true,
})
@Unique({ properties: ["benefitCode", "tenantId"] })
export class Benefit extends Base<Benefit> {

  @Field(() => STATUS)
  @Enum(() => STATUS)
  @Property({ columnType: "text" })
  public benefitStatus: STATUS;
}

const employee = await employeeRepository.findOneOrFail(
     readInput.employeeId,
     {
       strategy: LoadStrategy.SELECT_IN,
       filters: [FILTERS.CURRENT_DAY_OFF, FILTERS.IS_ACTIVE],
       populate: ["dayOffs", "benefits"],
     }
   );

[query] select "e0".* from "tbl_day_off" as "e0" where "e0"."employee_id" in ('00000000-0000-0000-0000-000000000001') and "e0"."tenant_id" = '1' and "e0"."date_to" is null order by "e0"."employee_id" asc [took 3 ms]

[query] select "e0".*, "e1"."benefit_id", "e1"."employee_id" from "tbl_benefit" as "e0" left join "tbl_employee_benefits" as "e1" on "e0"."id" = "e1"."benefit_id" where "e1"."employee_id" in ('00000000-0000-0000-0000-000000000001') [took 2 ms]

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 16 (8 by maintainers)

Commits related to this issue

test: manyToMany filter bug (#1232) — committed to jagabs/mikro-orm by jagabs 4 years ago

Most upvoted comments

FYI your repro was not 100% correct, it will never work like that as the collection was already loaded in the memory - you need to clear the EM before you load the entity again to replicate new request. Also you were not populating the collection at all, so if you would actually clear, it would not be even loaded.

Here is a fixed repro:

    const benefit = new Benefit();
    benefit.benefitStatus = 'IA';
    const employee = new Employee();
    employee.benefits.add(benefit);
    await orm.em.persistAndFlush(employee);
    orm.em.clear();

    const b1 = await orm.em.find(Benefit, {});
    expect(b1).toHaveLength(0);
    orm.em.clear();

    const e1 = await orm.em.findOneOrFail(Employee, employee.id, ['benefits']);
    expect(e1.benefits).toHaveLength(0);

B4nan on Mar 20, 2021

Interesting, let’s wait for the repro.

B4nan on Dec 27, 2020

Currently filters are applied only for the root entity, but you can filter the relation from there too. One of your filters is enabled by default - that is why it works, and that filters: [FILTERS.CURRENT_DAY_OFF, FILTERS.IS_ACTIVE], line in your query has no effect on that.

You could either enable both of them by default (and disable where needed), or specify the filters on the employee entity:

@Filter({
  name: 'IS_ACTIVE',
  cond: {
    benefits: { benefitStatus: { $eq: STATUS.ACTIVE } },
  },
})
@Filter({
  name: 'CURRENT_DAY_OFF',
  cond: {
    dayOffs: { dateTo: { $eq: null } },
  },
}) 
export class Employee {

  @ManyToMany(() => Benefit)
  public benefits?: Collection<Benefit> = new Collection<Benefit>(this);

  @OneToMany({
    entity: () => DayOff,
    mappedBy: 'employee',
    orphanRemoval: true,
  })
  public dayOffs = new Collection<DayOff>(this);

}

Then you can enable them when querying the employee:

const employee = await employeeRepository.findOneOrFail(
     readInput.employeeId,
     {
       strategy: LoadStrategy.SELECT_IN,
       filters: ['CURRENT_DAY_OFF', 'IS_ACTIVE'],
       populate: ["dayOffs", "benefits"],
     }
   );

B4nan on Dec 25, 2020