Mikro-orm: Many to Many relationship flush() || persistAndFlush() runs excessive deletion

[MikeAliG]

Hello,
In the process of persisting and flushing Many to Many relationships, both flush() and persistAndFlush() run unnecessary deletions before saving.

The Process:

We have Organisation Entity with the following model:

import {
  Collection,
  Entity,
  ManyToMany,
  OneToMany,
  Property,
  Unique,
} from "mikro-orm";

@Entity()
export class Organisation extends BaseEntity {
  @Property()
  name: string;

  @ManyToMany(() => Moderator, (mod) => mod.moderatorOf)
  mods: Collection<Moderator> = new Collection<Moderator>(this);

  @ManyToMany(() => Moderator, "founderOf", { owner: true })
  founders: Collection<Moderator> = new Collection<Moderator>(this);
}

And this is the Moderator Entity:

import { Collection, Entity, ManyToMany, OneToOne } from "mikro-orm";

@Entity()
export class Moderator extends BaseEntity {
  @OneToOne(() => User, (user) => user.mod)
  user: User;

  @ManyToMany(() => Organisation, (org) => org.founders)
  founderOf: Collection<Organisation> = new Collection<Organisation>(this);

  @ManyToMany(() => Organisation, "mods", { owner: true })
  moderatorOf: Collection<Organisation> = new Collection<Organisation>(this);

}

And once we save the relationship like this:

  async createOrganisation(
  ) {
    const user = context.db.em.getReference(User, context.req.session.userId);
    const orgRepo = context.db.em.getRepository(Organisation);
    const modRepo = context.db.em.getRepository(Moderator);

    const { name, bio, categories } = input;
    const org = new Organisation({
      name,
      bio,
      categories: categories.toString(),
      slug: slugify(name, { lower: true }),
    });

    await orgRepo.persist(org);
    const mod = await modRepo.findOne({ user: { id: user.id } });
    if (!mod) {
      throw new Error("User Was Not Found!");
    }
    await mod.moderatorOf.init();
    mod.moderatorOf.add(org);
    org.founders.add(mod);
    await context.db.em.flush();
    return org;
  }

This is the debug's output:

[query] begin
[query] insert into "organisation" ("bio", "categories", "created_at", "id", "name", "slug", "updated_at") values ('We are GG Team', 'JS,GG,GQL', '2020-05-08 02:56:37.345', 'e7952e75-129a-489f-baa4-bd89017c0e0a', 'GG2', 'gg2', '2020-05-08 02:56:37.345') returning "id", "threshold" [took 4 ms]
[query] commit
[query] select "e0".*, "e1"."id" as "user_id" from "moderator" as "e0" left join "user" as "e1" on "e0"."id" = "e1"."mod_id" where "e1"."id" = '18527b0a-4da0-4e36-8a45-1fcc43767342' limit 1 [took 14 ms]
[query] select "e0".* from "organisation" as "e0" where "e0"."id" in ('e46453c7-bbb2-4862-b2af-9349205f8384', 'da1f6e9f-c269-47f2-8d35-92ad95b8f87e') [took 12 ms]
[query] begin
// This section ⬇️
[query] delete from "moderator_to_organisation" where ("organisation_id") in (('e46453c7-bbb2-4862-b2af-9349205f8384'), ('da1f6e9f-c269-47f2-8d35-92ad95b8f87e')) and "moderator_id" = 'b79c344a-ba7b-4d7e-9ad8-bfd3c022bbfc' [took 10 ms]
[query] insert into "moderator_to_organisation" ("moderator_id", "organisation_id") values ('b79c344a-ba7b-4d7e-9ad8-bfd3c022bbfc', 'e46453c7-bbb2-4862-b2af-9349205f8384'), ('b79c344a-ba7b-4d7e-9ad8-bfd3c022bbfc', 'da1f6e9f-c269-47f2-8d35-92ad95b8f87e'), ('b79c344a-ba7b-4d7e-9ad8-bfd3c022bbfc', 'e7952e75-129a-489f-baa4-bd89017c0e0a') [took 6 ms]
[query] insert into "organisation_to_moderator" ("moderator_id", "organisation_id") values ('b79c344a-ba7b-4d7e-9ad8-bfd3c022bbfc', 'e7952e75-129a-489f-baa4-bd89017c0e0a') [took 7 ms]
[query] commit

which essentially deletes the relative ids from moderator_to_organisation and adds them back in. Is there anyway to fix it? Am I doing something wrong or anti-pattern?

Many Thanks In Advance.

Edit: Cleaned the code.

[B4nan]

What version are you using? The issue template has its purpose...

Collection diffing was added in 3.5 via #405

But looking at the table names, your problem will be owning/inverse side setup, there should be only one table for given relation and it looks like you have two (moderator_to_organisation and organisation_to_moderator).

[B4nan]

I think your problem is the name of pivot table, as you are using multiple M:N relations between the same entities, you will need to manually specify the name on owning sides.

This is fixed in v4, as the naming strategy for pivot tables has changed.

https://github.com/mikro-orm/mikro-orm/commit/99d7bdf4fb25be926892fd4ee4ff5fc3555ac8a5

[B4nan]

I don't understand what you are trying to model there. Do you really want to have 2 different relations between org and moderator (so 2 join tables)? Also for simplicity it would be good to remove unrelated code from your example, e.g. the QGL parts are giving me quite a headache, also that user entity just adds clutter.

After spending almost two hours staring at the code I still don't understand what you are trying there.

I am quite sure it is just a wrong setup, in terms of what is owning side and what not. And to be honest, calling a relation "moderatorOf" really sounds like that should not be owning side. It feels like you were struggling with the metadata validation and you just randomly moved the owner: true until it stopped complaining? :]

I would expect the org entity to be owner, if you really want to have 2 distinct m:n relations (founders and moderators), then for both of them.

@Entity()
export class Organisation extends BaseEntity {

  // owning side (no mapped by provided)
  @ManyToMany({ entity: () => Moderator, pivotTable: 'org_moderators' })
  mods: Collection<Moderator> = new Collection<Moderator>(this);

  // owning side (no mapped by provided)
  @ManyToMany({ entity: () => Moderator, pivotTable: 'org_founders' })
  founders: Collection<Moderator> = new Collection<Moderator>(this);

}

@Entity()
export class Moderator extends BaseEntity {

  @ManyToMany(() => Organisation, 'founders')
  founderOf: Collection<Organisation> = new Collection<Organisation>(this);

  @ManyToMany(() => Organisation, 'mods')
  moderatorOf: Collection<Organisation> = new Collection<Organisation>(this);

}

const org = new Organisation({ ... });
em.persist(org);
const mod = await em.findOneOrFail(Moderator, { user: user.id });
org.mods.add(mod);
org.founders.add(mod);
await em.flush();

return org;

[MikeAliG]

Hello!
Thanks a lot! You are 100% correct. The pivot tables were overlapping.

I don't understand what you are trying to model there. Do you really want to have 2 different relations between org and moderator (so 2 join tables)? Also for simplicity it would be good to remove unrelated code from your example, e.g. the QGL parts are giving me quite a headache, also that user entity just adds clutter.

After spending almost two hours staring at the code I still don't understand what you are trying there.

I'm extremely sorry about that. I was working late last night and I didn't know if this was a bug or my mistake, turns out it was my mistake but nevertheless, that's why I didn't choose a template. And I know it's a bit late now, but I cleaned up the original post's code for future reference.

I am quite sure it is just a wrong setup, in terms of what is owning side and what not. And to be honest, calling a relation "moderatorOf" really sounds like that should not be owning side. It feels like you were struggling with the metadata validation and you just randomly moved the owner: true until it stopped complaining? :]

😄 Exactly! And because of that (having the moderator become the owner) It was removing everything from one table and putting everything back in.

I think your problem is the name of pivot table, as you are using multiple M:N relations between the same entities, you will need to manually specify the name on owning sides.

That was exactly true. Can I make a PR to the Docs and put that under https://mikro-orm.io/docs/relationships#manytomany section?

Thanks a lot for all your help. You are amazing.

[B4nan]

Can I make a PR to the Docs and put that under https://mikro-orm.io/docs/relationships#manytomany section?

Yeah sure, as I said, this will change in v4, but we should have a note in v3 docs. So please target master branch and adjust v3 docs (those in versioned directory).