Connecting 3 separate tables with a single through table in Sequelize

Viewed 40

I'm trying to figure out why this is not working as intended. Any insights would be appreciated. Here's my situation:

I have a legacy connection of two tables with a through table. Something like:

Product model

const Product = sequelize.define('Product', {
  // model attributes
});

Product.associate = (models) => {
  Product.belongsToMany(models.Client, { through: models.ProductOwner, as: 'Owners'});
}

Client model

const Client = sequelize.define('Client', {
  // model attributes
});

Client.associate = (models) => {
  Client.belongsToMany(models.Product, { through: models.ProductOwner, as: 'OwnedProducts'});
}

ProductOwner

const ProductOwner = sequelize.define('Product', {
  // no attributes
});

These form the N:M association with the through table ProductOwner. This allows me, for example, to easily add a client to a product or getting all products that an existing client owns.

product.addOwner(client);
client.getOwnedProducts();

Now, I have the need to establish another chain of ownership to products that is unrelated to client. However, since this is still ownership, I would like to use the ProductOwner through table. So I add the new model:

Company model

const Company = sequelize.define('Company', {
  // model attributes
});

Company.associate = (models) => {
  Company.belongsToMany(models.Product, { through: models.ProductOwner, as: 'OwnedProducts'})
}

And the new association to the Products model. I also write a migration to add the CompanyId to the ProductOwners table and verify that the new reference is built into the database.

Product model

const Product = sequelize.define('Product', {
  // model attributes
});

Product.associate = (models) => {
  Product.belongsToMany(models.Client, { through: models.ProductOwner, as: 'Owners'});
  Product.belongsToMany(models.Company, { 
    through: models.ProductOwner, as: 'CompanyOwners'
  });
}

Now, on my code, I should be able to write:

product.addCompanyOwner(company);
company.getOwnedProducts();

And indeed, using the product instance method to add a new company does not throw any errors. However, the CompanyId column in the ProductOwners through table is still NULL. Logging the query generated by Sequelize I see that the references to ProductId and ClientId are there, but there is no mention of CompanyId. Looks as if it is not recognizing that a new reference exists from Sequelize's point of view. However, the instance methods do work...

Which brings me to the question of why do they work? I assume that, by working, Sequelize is indeed creating the associations. But if that is the case, then why does the value for CompanyId is not set with the query?

Even writing it explicitly does not produce the expect result of setting CompanyId...

db.ProductOwner.create({
  ProductId: 1,
  ClientId: 1
}) // works to set all values

db.ProductOwner.create({
  ProductId: 1,
  CompanyId: 1
}) // sets ProductId to 1, but CompanyId is still NULL

What am I missing?

0 Answers
Related