Recursive many to many smart relationship

Help me!
1

I haven’t found a way to display what I need to.

I basically have 3 tables:

  • parcels
  • parcelContracts
  • contracts (which is an owner)

Technically, the DB allows that :

  • A parcel can be owned by several contracts
  • A contract can own several parcels

Hence I have the following relationships:

Contracts.belongsToMany(models.parcels, {
      through: 'parcelContracts',
      foreignKey: 'contract_id',
      otherKey: 'parcel_id',
      as: 'parcelsThroughParcelContracts',
});

Parcels.belongsToMany(models.contracts, {
      through: 'parcelContracts',
      foreignKey: 'parcel_id',
      otherKey: 'contract_id',
      as: 'contractsThroughParcelContracts',
});

My goal : while I am on a specific parcel summary view, I need to display the list of parcels that belongs to this parcel’s contracts. Even if the DB allows a parcel to be owned by several contracts, the should never be the case, any parcel should belong to one contract only.

I built a smart relationship but when it comes to the query, I can’t use the existing relationship above to specify from which parcel i’m coming from.
I’ve been trying to split into 2 relationships the relations above, such as :

Parcels.hasMany(models.parcelContracts, {
      foreignKey: {
        name: 'parcelIdKey',
        field: 'parcel_id',
      },
      as: 'parcelContract',
});

and

Contracts.hasMany(models.parcelContracts, {
      foreignKey: {
        name: 'contractIdKey',
        field: 'contract_id',
      },
      as: 'contractParcel',
});

and wrote a query as follows:

const include = [{
    model: models.parcelContracts,
    as: 'parcelContract',
    include: [{
      model: models.contracts,
      as: 'contract',
      include: [{
        model: models.parcelContracts,
        as: 'contractParcel',
        where: { parcel_id: parcelId }
      }]
    }]
  }];

but I get a error message :

SequelizeDatabaseError: missing FROM-clause entry for table "parcelContract"

Can anybody help to figure this out ?

1 Like
2

Could you try this instead in your findAll query?

subQuery: false,
const where = { ['$parcelContract.contract.contractParcel.parcel_id$']: parcelId },
const include = [{
    model: models.parcelContracts,
    as: 'parcelContract',
    include: [{
      model: models.contracts,
      as: 'contract',
      include: [{
        model: models.parcelContracts,
        as: 'contractParcel',
        // where: { parcel_id: parcelId }
      }]
    }]
  }];

Let me know if this helps,

Cheers,

3

Here’s what I wrote :

  const where = { ['$parcelContract.contract.contractParcel.parcel_id$']: parcelId };
  const include = [{
    model: models.parcelContracts,
    as: 'parcelContract',
    include: [{
      model: models.contracts,
      as: 'contract',
      include: [{
        model: models.parcelContracts,
        as: 'contractParcel',
      }]
    }]
  }];

  // find the parcels for the requested page and page size
  const findAll = models.parcels.findAll({
    where,
    include,
    offset,
    limit,
  });

  // count all parcels for pagination
  const count = models.parcels.count({ include, where });

Unfortunately @anon20071947 I still get an error message :
SequelizeDatabaseError: missing FROM-clause entry for table "parcelContract->contract->contractParcel"

1 Like
4

Couple of things here,

  • Can you add the subQuery: false, option please (before the where in the .findAll({...}))? :pray:t2:
  • Can you add NODE_ENV=development to your .env and paste here the full SQL generated query, that’s the only we’ll find out what’s wrong

Thanks for your help!

1 Like
5

Hey @anon20071947 by putting subQuery: false it seems to be working without error. I didn’t know about this option and will read about it.

I’ll check the data and will let you know if everything is working fine.

1 Like
6

Glad it did :tada: This is not even documented in the Sequelize API reference :man_shrugging:t2:

One last thing, you should probably use .findAndCountAll() to get the correct count in one shot :wink:

3 Likes