Relationship: Many-to-many (self relation)

[adm-bome]

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?

[CodeDredd]

This is working now. Seems some of my changes fixed this.

[adm-bome]

@CodeDredd

Please see my latest comment in #1447