Relationship: Many-to-many (self relation) [Followup on issue #1447]

[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.

Originally posted by @adm-bome in #1447 (comment)

[adm-bome]

I'm testing this in Quasar framework

// ./pages/IndexPage.vue  [<script>] section
import { defineComponent } from 'vue'
import {Model, useRepo} from "pinia-orm";

const storeData = (repo) => {
  return JSON.parse(JSON.stringify(repo.piniaStore().$state.data))
}

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'),
    }
  }
}

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)
    }
  }
}

const rawData = [
  {
    id: 1,
    name: "Client 1",
    retailers: [
      {
        id: 4,
        pivot: {
          retailerCode: '401'
        }
      },
      {
        id: 5,
        pivot: {
          retailerCode: '501'
        }
      }
    ]
  },
  {
    id: 2,
    name: "Client 2",
    retailers: [
      {
        id: 3,
        pivot: {
          retailerCode: '302'
        }
      },
      {
        id: 5,
        pivot: {
          retailerCode: '502'
        }
      }
    ]
  },
  {
    id: 3,
    name: "Client 3"
  },
  {
    id: 4,
    name: "Client 4",
    retailers: [
      {
        id: 1,
        pivot: {
          retailerCode: '104'
        }
      },
      {
        id: 5,
        pivot: {
          retailerCode: '504'
        }
      }
    ]
  },
  {
    id: 5,
    name: "Client 5"
  },
]

export default defineComponent({
  name: 'IndexPage',
  mounted() {
    const clientRepo = useRepo(Client)
    const clientRetailerRepo = useRepo(ClientRetailer)

    clientRepo.save(rawData)
    console.log('Store: client_retailer', storeData(clientRetailerRepo))
  }
})

// ./stores/index.js
import { store } from 'quasar/wrappers'
import { createPinia } from 'pinia'
import { createORM, Model} from 'pinia-orm'

export const Store = createPinia()

// You can add Pinia plugins here
Store.use(createORM())

export default store((/* { ssrContext } */) => {
  return Store
})

The result for the store: client_retailer

{
    "[4,4]": { "supplierId": 4, "retailerId": 4, "retailerCode": "401"    },
    "[5,5]": { "supplierId": 5, "retailerId": 5, "retailerCode": "504"    },
    "[3,3]": { "supplierId": 3, "retailerId": 3, "retailerCode": "302"    },
    "[1,1]": { "supplierId": 1, "retailerId": 1, "retailerCode": "104"    }
}

The expected result in the store should be (always):

{
    "[1,4]": { "supplierId": 1, "retailerId": 4, "retailerCode": "401" },
    "[1,5]": { "supplierId": 1, "retailerId": 5, "retailerCode": "501" },
    "[2,3]": { "supplierId": 2, "retailerId": 3, "retailerCode": "302" },
    "[2,5]": { "supplierId": 2, "retailerId": 5, "retailerCode": "502" },
    "[4,1]": { "supplierId": 4, "retailerId": 1, "retailerCode": "104" },
    "[4,5]": { "supplierId": 4, "retailerId": 5, "retailerCode": "504" }
}

And when a Client requests the retailers, all retailers should be returned
And when a Client requests the suppliers, all suppliers should be returned

[CodeDredd]

@adm-bome Ufff i see what's happing. Now i understand the edge case.... fix on the way

[adm-bome]

I will test the sollution. when available.

I was digging and testing just now... to find the sollution.
I noticed, the getId() was returning the wrong combined key.
And within the attach() method on a belongsToMany relation, the identifier properties were wrongly set.

Thanks for the quick fix. I was almost there ;)

[CodeDredd]

Your welcome.

I will test the sollution. when available.

Don't forget that you can test it with the edge channel. 😉

[adm-bome]

Applied your code manually. It looks promising.

Question: Is there a reason all (behind the scene) pivot fields are not deleted or removed.

When I query pinia with

clientRepo.whereId(5).with('suppliers').first()

I get this result:

{
    id: 5,
    name: "Client 5",
    pivot: undefined,
    suppliers: [
        {
            pivot: {
                supplierId: 1,
                retailerId: 5,
                retailerCode: "501"
            },
            pivot_retailerId_client_retailers: null,
            pivot_supplierId_client_retailers: null,
            id: 1,
            name: "Client 1",
            retailers: [],
            suppliers: []
        },
        {
            pivot: {
                supplierId: 2,
                retailerId: 5,
                retailerCode: "502"
            },
            pivot_retailerId_client_retailers: null,
            pivot_supplierId_client_retailers: null,
            id: 2,
            name: "Client 2",
            retailers: [],
            suppliers: []
        },
        {
            pivot: {
                supplierId: 4,
                retailerId: 5,
                retailerCode: "504"
            },
            pivot_retailerId_client_retailers: null,
            pivot_supplierId_client_retailers: null,
            id: 4,
            name": "Client 4",
            retailers: [],
            suppliers: []
        }
    ]
}

See the top level pivot: undefined and all pivot_*: null properties on relations

Expected result:

{
    id: 5,
    name: "Client 5",
    suppliers: [
        {
            pivot: {
                supplierId: 1,
                retailerId: 5,
                retailerCode: "501"
            },
            id: 1,
            name: "Client 1",
            retailers: [],
            suppliers: []
        },
        {
            pivot: {
                supplierId: 2,
                retailerId: 5,
                retailerCode: "502"
            },
            id: 2,
            name: "Client 2",
            retailers: [],
            suppliers: []
        },
        {
            pivot: {
                supplierId: 4,
                retailerId: 5,
                retailerCode: "504"
            },
            id: 4,
            name": "Client 4",
            retailers: [],
            suppliers: []
        }
    ]
}

[adm-bome]

Another thingy:

When i query pinia with:

clientRepo.whereId(5).withAll().first()

The result is:

{
  id: 5,
  name: "Client 5",
  pivot: {
    supplierId: 5,
    retailerId: 4,
    retailerCode: "405"
  },
  retailers: [
    {
      pivot: {
        supplierId: 5,
        retailerId: 2,
        retailerCode: "205"
      },
      pivot_retailerId_client_retailers: null,
      pivot_supplierId_client_retailers: null,
      id: 2,
      name: "Client 2",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 5,
        retailerId: 4,
        retailerCode: "405"
      },
      pivot_retailerId_client_retailers: null,
      pivot_supplierId_client_retailers: null,
      id: 4,
      name: "Client 4",
      retailers: [],
      suppliers: []
    }
  ],
  suppliers: [
    {
      pivot: {
        supplierId: 1,
        retailerId: 5,
        retailerCode: "501"
      },
      pivot_retailerId_client_retailers: null,
      pivot_supplierId_client_retailers: null,
      id: 1,
      name: "Client 1",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 2,
        retailerId: 5,
        retailerCode: "502"
      },
      pivot_retailerId_client_retailers: null,
      pivot_supplierId_client_retailers: null,
      id: 2,
      name: "Client 2",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 4,
        retailerId: 5,
        retailerCode: "504"
      },
      pivot_retailerId_client_retailers: null,
      pivot_supplierId_client_retailers: null,
      id: 4,
      name: "Client 4",
      retailers: [],
      suppliers: []
    }
  ]
}

See also the toplevel pivot property. Why? Because it makes no sense and I don't think this is what people want.

Expected result: without all pivot mess

{
  id: 5,
  name: "Client 5",
  retailers: [
    {
      pivot: {
        supplierId: 5,
        retailerId: 2,
        retailerCode: "205"
      },
      id: 2,
      name: "Client 2",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 5,
        retailerId: 4,
        retailerCode: "405"
      },
      id: 4,
      name: "Client 4",
      retailers: [],
      suppliers: []
    }
  ],
  suppliers: [
    {
      pivot: {
        supplierId: 1,
        retailerId: 5,
        retailerCode: "501"
      },
      id: 1,
      name: "Client 1",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 2,
        retailerId: 5,
        retailerCode: "502"
      },
      id: 2,
      name: "Client 2",
      retailers: [],
      suppliers: []
    },
    {
      pivot: {
        supplierId: 4,
        retailerId: 5,
        retailerCode: "504"
      },
      id: 4,
      name: "Client 4",
      retailers: [],
      suppliers: []
    }
  ]
}