BelongsToMany relation returns a toplevel 'pivot' relation, when non is expected

[adm-bome]

See the toplevel pivot property. Why, is a wrongly intermediate model assigned to the Model.

In my opinion: This makes no sense and I don't think this is what people want. The top level Model has no direct relation to intermediate model.

// Setup:
import {Model, useRepo} from "pinia-orm";

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 = ['supplierId','retailerId']

  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",
    retailers: [
      {
        id: 4,
        pivot: {
          retailerCode: '405'
        }
      },
      {
        id: 2,
        pivot: {
          retailerCode: '205'
        }
      }
    ]
  },
]

When i query pinia with:

const clientRepo = useRepo(Client)
clientRepo.save(rawData)
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: []
    }
  ]
}

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: []
    }
  ]
}

Originally posted by @adm-bome in https://github.com/CodeDredd/pinia-orm/issues/1857#issuecomment-2129497142