Relationship: Many-to-many (self relation)

[adm-bome]

Discussed in #1443

^{Originally posted by adm-bome July 5, 2023}
I hope i'm explaining this correctly

We have a model called Client.
A client can have multiple retailers (which are also clients).
A client can have multiple suppliers (which are also clients, defined if a client has retailers)

So a Client has one or many retailers, which then result in one ore more suppliers for a client.

Also known as a: Many-to-many self relation

// Intermediate Model
export default class ClientRetailer extends Model {
  static entity = 'client_retailers'

  static primaryKey = ['retailerId', 'supplierId']

  static fields () {
    return {
      supplierId: this.number(null),
      retailerId: this.number(null),
      retailerCode: this.string(null)
    }
  }
}

// Client Model
export default class Client extends Model {
  static entity = 'clients'

  static fields () {
    return {
      id: this.number(0),
      name: this.string(null),
      retailers: this.belongsToMany(Client, ClientRetailer, 'supplierId', 'retailerId')

    }
  }
}

Now for the funky part...

Now we want to look up the inverse of this relationship.
So when we have a Client which is actually a retailer. We want to look up all suppliers this client/retailer has.
So we defined the inverse relation for retailers.

// Client Model
export default class Client extends Model {
  static entity = 'clients'

  static fields () {
    return {
      id: this.number(0),
      name: this.string(null),
      retailers: this.belongsToMany(Client, ClientRetailer, 'supplierId', 'retailerId'),
      suppliers: this.belongsToMany(Client, ClientRetailer, 'retailerId', 'supplierId')
    }
  }
}

The result:
Meshed-up data and primarykey in the ClientRetailer. So we can't determine the relations anymore.

// data stored/saved into a Client
const rawData = [
  {
    "id": 1,
    "name": "Client 1",
    "retailers": [
      {
        "id": 16,
        "name": "Client 16",
        "pivot": {
          "retailer_code": '555'
        }
      },
      {
        "id": 18,
        "name": "Client 18",
        "pivot": {
          "retailer_code": '666'
        }
      }
    ]
  }
]

useRepo(ClientRepository).save(rawData)

What are we missing?
Is this even posible?

[adm-bome]

Also tryed the following as a intermediate model.
The result is some what different, but still no luck.

export default class ClientRetailer extends Model {
  static entity = 'client_retailers'

  static fields () {
    return {
      id: this.uid(),
      supplierId: this.number(null),
      retailerId: this.number(null),
      retailerCode: this.string(null)
    }
  }
}

[CodeDredd]

@adm-bome This seems to be fixed with the latest version. Tried out on an test. If not please reopen it.

Update
...ok i take it back. some data is not right....fixing it.

[CodeDredd]

ok i found out that everything works fine. You got an typo. One time you use "retailerCode" and in the rawData "retailer_code". I already wondered why it is not saved.

[adm-bome]

@CodeDredd: i found out that everything works fine. You got an typo. One time you use "retailerCode" and in the rawData "retailer_code".

My issue was not the retailerCode or retailer_code.

The retailerId and supplierId should be different:

The result supplierId: 1 and retailerId: 16 The index should be [16, 1]

This works great if a developer only uses one belongsToMany relation; like in your test.

class Client extends Model {
  static entity = 'clients'

  static fields () {
    return {
      id: this.number(0),
      name: this.string(null),
      retailers: this.belongsToMany(Client, ClientRetailer, 'supplierId', 'retailerId'),
    }
  }
}

When we add a second "inversed relation" on the Client model. Because when we have a Client and we want to know if the Client has suppliers. Things go wrong.

class Client extends Model {
  static entity = 'clients'

  static fields () {
    return {
      id: this.number(0),
      name: this.string(null),
      retailers: this.belongsToMany(Client, ClientRetailer, 'supplierId', 'retailerId'),
      suppliers: this.belongsToMany(Client, ClientRetailer, 'retailerId', 'supplierId')
    }
  }
}

The retailerId and supplierId should still be different:

The result supplierId: 1 and retailerId: 16 The index should still be [16, 1]
not [16,16]

What bothers me is that in the rawData the relation suppliers the data was never provided.
And it looks like despite the absense of data the relation was still called or filled.