This has a sibling question in Software Engineering SE.
Consider Company, Product and Person.
There is a many-to-many relationship between Company and Product, through a junction table Company_Product, because a given pany may produce more than one product (such as "car" and "bicycle"), but also a given product, such as "car", can be produced by multiple panies. In the junction table Company_Product there is an extra field "price" which is the price in which the given pany sells the given product.
There is another many-to-many relationship between Company_Product and Person, through a junction table Company_Product_Person. Yes, it is a many-to-many relationship involving one entity that is already a junction table. This is because a Person can own multiple products, such as a car from pany1 and a bicycle from pany2, and in turn the same pany_product can be owned by more than one person, since for example both person1 and person2 could have bought a car from pany1. In the junction table Company_Product_Person there is an extra field "thoughts" which contains the thoughts of the person at the moment they purchased the pany_product.
I want to make a query with sequelize to get from the database all instances of Company, with all related Products with the respective Company_Product which in turn include all related Persons with the respective Company_Product_Persons.
Getting the elements of both junction tables is important too, because the fields "price" and "thoughts" are important.
And I was not able to figure out how to do this.
I made the code as short as I could to investigate this. Looks big, but most of it is model declaration boilerplate: (to run it, first do npm install sequelize sqlite3)
const Sequelize = require("sequelize");
const sequelize = new Sequelize({ dialect: "sqlite", storage: "db.sqlite" });
// ================= MODELS =================
const Company = sequelize.define("pany", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Product = sequelize.define("product", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Person = sequelize.define("person", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Company_Product = sequelize.define("pany_product", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
panyId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "pany",
key: "id"
},
onDelete: "CASCADE"
},
productId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "product",
key: "id"
},
onDelete: "CASCADE"
},
price: Sequelize.INTEGER
});
const Company_Product_Person = sequelize.define("pany_product_person", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
panyProductId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "pany_product",
key: "id"
},
onDelete: "CASCADE"
},
personId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "person",
key: "id"
},
onDelete: "CASCADE"
},
thoughts: Sequelize.STRING
});
// ================= RELATIONS =================
// Many to Many relationship between Company and Product
Company.belongsToMany(Product, { through: "pany_product", foreignKey: "panyId", onDelete: "CASCADE" });
Product.belongsToMany(Company, { through: "pany_product", foreignKey: "productId", onDelete: "CASCADE" });
// Many to Many relationship between Company_Product and Person
Company_Product.belongsToMany(Person, { through: "pany_product_person", foreignKey: "panyProductId", onDelete: "CASCADE" });
Person.belongsToMany(Company_Product, { through: "pany_product_person", foreignKey: "personId", onDelete: "CASCADE" });
// ================= TEST =================
var pany, product, person, pany_product, pany_product_person;
sequelize.sync({ force: true })
.then(() => {
// Create one pany, one product and one person for tests.
return Promise.all([
Company.create({ name: "Company test" }).then(created => { pany = created }),
Product.create({ name: "Product test" }).then(created => { product = created }),
Person.create({ name: "Person test" }).then(created => { person = created }),
]);
})
.then(() => {
// pany produces product
return pany.addProduct(product);
})
.then(() => {
// Get the pany_product for tests
return Company_Product.findAll().then(found => { pany_product = found[0] });
})
.then(() => {
// person owns pany_product
return pany_product.addPerson(person);
})
.then(() => {
// I can get the list of Companys with their Products, but couldn't get the nested Persons...
return Company.findAll({
include: [{
model: Product
}]
}).then(panies => {
console.log(JSON.stringify(panies.map(pany => pany.toJSON()), null, 4));
});
})
.then(() => {
// And I can get the list of Company_Products with their Persons...
return Company_Product.findAll({
include: [{
model: Person
}]
}).then(panyproducts => {
console.log(JSON.stringify(panyproducts.map(panyproduct => panyproduct.toJSON()), null, 4));
});
})
.then(() => {
// I should be able to make both calls above in one, getting those nested things
// at once, but how??
return Company.findAll({
include: [{
model: Product
// ???
}]
}).then(panies => {
console.log(JSON.stringify(panies.map(pany => pany.toJSON()), null, 4));
});
});
My goal is to obtain an array of Companys already with all the deep-nested Persons and Company_Product_Persons at one go:
// My goal:
[
{
"id": 1,
"name": "Company test",
"createdAt": "...",
"updatedAt": "...",
"products": [
{
"id": 1,
"name": "Product test",
"createdAt": "...",
"updatedAt": "...",
"pany_product": {
"id": 1,
"panyId": 1,
"productId": 1,
"price": null,
"createdAt": "...",
"updatedAt": "...",
"persons": [
{
"id": 1,
"name": "Person test",
"createdAt": "...",
"updatedAt": "...",
"pany_product_person": {
"id": 1,
"panyProductId": 1,
"personId": 1,
"thoughts": null,
"createdAt": "...",
"updatedAt": "..."
}
}
]
}
}
]
}
];
How can I do this?
Note: I could make both queries separately and write some code to "join" the retrieved objects, but that would be putationally expensive and ugly. I am looking for the right way to do this.
This has a sibling question in Software Engineering SE.
Consider Company, Product and Person.
There is a many-to-many relationship between Company and Product, through a junction table Company_Product, because a given pany may produce more than one product (such as "car" and "bicycle"), but also a given product, such as "car", can be produced by multiple panies. In the junction table Company_Product there is an extra field "price" which is the price in which the given pany sells the given product.
There is another many-to-many relationship between Company_Product and Person, through a junction table Company_Product_Person. Yes, it is a many-to-many relationship involving one entity that is already a junction table. This is because a Person can own multiple products, such as a car from pany1 and a bicycle from pany2, and in turn the same pany_product can be owned by more than one person, since for example both person1 and person2 could have bought a car from pany1. In the junction table Company_Product_Person there is an extra field "thoughts" which contains the thoughts of the person at the moment they purchased the pany_product.
I want to make a query with sequelize to get from the database all instances of Company, with all related Products with the respective Company_Product which in turn include all related Persons with the respective Company_Product_Persons.
Getting the elements of both junction tables is important too, because the fields "price" and "thoughts" are important.
And I was not able to figure out how to do this.
I made the code as short as I could to investigate this. Looks big, but most of it is model declaration boilerplate: (to run it, first do npm install sequelize sqlite3)
const Sequelize = require("sequelize");
const sequelize = new Sequelize({ dialect: "sqlite", storage: "db.sqlite" });
// ================= MODELS =================
const Company = sequelize.define("pany", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Product = sequelize.define("product", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Person = sequelize.define("person", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
name: Sequelize.STRING
});
const Company_Product = sequelize.define("pany_product", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
panyId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "pany",
key: "id"
},
onDelete: "CASCADE"
},
productId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "product",
key: "id"
},
onDelete: "CASCADE"
},
price: Sequelize.INTEGER
});
const Company_Product_Person = sequelize.define("pany_product_person", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
panyProductId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "pany_product",
key: "id"
},
onDelete: "CASCADE"
},
personId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "person",
key: "id"
},
onDelete: "CASCADE"
},
thoughts: Sequelize.STRING
});
// ================= RELATIONS =================
// Many to Many relationship between Company and Product
Company.belongsToMany(Product, { through: "pany_product", foreignKey: "panyId", onDelete: "CASCADE" });
Product.belongsToMany(Company, { through: "pany_product", foreignKey: "productId", onDelete: "CASCADE" });
// Many to Many relationship between Company_Product and Person
Company_Product.belongsToMany(Person, { through: "pany_product_person", foreignKey: "panyProductId", onDelete: "CASCADE" });
Person.belongsToMany(Company_Product, { through: "pany_product_person", foreignKey: "personId", onDelete: "CASCADE" });
// ================= TEST =================
var pany, product, person, pany_product, pany_product_person;
sequelize.sync({ force: true })
.then(() => {
// Create one pany, one product and one person for tests.
return Promise.all([
Company.create({ name: "Company test" }).then(created => { pany = created }),
Product.create({ name: "Product test" }).then(created => { product = created }),
Person.create({ name: "Person test" }).then(created => { person = created }),
]);
})
.then(() => {
// pany produces product
return pany.addProduct(product);
})
.then(() => {
// Get the pany_product for tests
return Company_Product.findAll().then(found => { pany_product = found[0] });
})
.then(() => {
// person owns pany_product
return pany_product.addPerson(person);
})
.then(() => {
// I can get the list of Companys with their Products, but couldn't get the nested Persons...
return Company.findAll({
include: [{
model: Product
}]
}).then(panies => {
console.log(JSON.stringify(panies.map(pany => pany.toJSON()), null, 4));
});
})
.then(() => {
// And I can get the list of Company_Products with their Persons...
return Company_Product.findAll({
include: [{
model: Person
}]
}).then(panyproducts => {
console.log(JSON.stringify(panyproducts.map(panyproduct => panyproduct.toJSON()), null, 4));
});
})
.then(() => {
// I should be able to make both calls above in one, getting those nested things
// at once, but how??
return Company.findAll({
include: [{
model: Product
// ???
}]
}).then(panies => {
console.log(JSON.stringify(panies.map(pany => pany.toJSON()), null, 4));
});
});
My goal is to obtain an array of Companys already with all the deep-nested Persons and Company_Product_Persons at one go:
// My goal:
[
{
"id": 1,
"name": "Company test",
"createdAt": "...",
"updatedAt": "...",
"products": [
{
"id": 1,
"name": "Product test",
"createdAt": "...",
"updatedAt": "...",
"pany_product": {
"id": 1,
"panyId": 1,
"productId": 1,
"price": null,
"createdAt": "...",
"updatedAt": "...",
"persons": [
{
"id": 1,
"name": "Person test",
"createdAt": "...",
"updatedAt": "...",
"pany_product_person": {
"id": 1,
"panyProductId": 1,
"personId": 1,
"thoughts": null,
"createdAt": "...",
"updatedAt": "..."
}
}
]
}
}
]
}
];
How can I do this?
Note: I could make both queries separately and write some code to "join" the retrieved objects, but that would be putationally expensive and ugly. I am looking for the right way to do this.
Share Improve this question edited Feb 16, 2018 at 13:04 Pedro A asked Feb 9, 2018 at 19:42 Pedro APedro A 4,3435 gold badges35 silver badges65 bronze badges1 Answer
Reset to default 7OP here.
Short answer
The key to the solution is to rethink the associations. Change the associations to:
Company.hasMany(Company_Product, { foreignKey: "panyId" });
Company_Product.belongsTo(Company, { foreignKey: "panyId" });
Product.hasMany(Company_Product, { foreignKey: "productId" });
Company_Product.belongsTo(Product, { foreignKey: "productId" });
Company_Product.hasMany(Company_Product_Person, { foreignKey: "panyProductId" });
Company_Product_Person.belongsTo(Company_Product, { foreignKey: "panyProductId" });
Person.hasMany(Company_Product_Person, { foreignKey: "personId" });
Company_Product_Person.belongsTo(Person, { foreignKey: "personId" });
Change return pany.addProduct(product); to
return Company_Product.create({
panyId: pany.id,
productId: product.id,
price: 99
}).then(created => { pany_product = created });
Change return pany_product.addPerson(person) to
return Company_Product_Person.create({
panyProductId: pany_product.id,
personId: person.id,
thoughts: "nice"
}).then(created => { pany_product_person = created });
The query that answers the question is
Company.findAll({
include: [{
model: Company_Product,
include: [{
model: Product
}, {
model: Company_Product_Person,
include: [{
model: Person
}]
}]
}]
})
The resulting JSON structure is not exactly the "goal" mentioned in question but it's just a matter of re-ordering.
Long answer
I found a solution that involves reworking the associations between the tables, even though the associations given in the question aren't technically wrong. A new way to see the problem, changing the associations, was the key to find a way to do what I wanted.
Analyzing the old approach
First of all, both junction tables given in my question were more than "just" junction tables. They weren't simply a tool to define which elements were related to which elements, but they were something more:
They also had extra information (the fields "price" and "thoughts", respectively);
The first one,
Company_Product, also had relationships with other tables itself.
This is not technically wrong, strictly speaking, but there is a more natural way to structure the database to represent the same things. And better, with this new approach, making the query I want bees very simple.
Solution: new approach
The solution rises when we see that we are modeling items that can be purchased and the purchases themselves. Instead of keeping this information "disguised" inside the junction table of a many-to-many relationship, we shall have them as explicit entities in our scheme, with their own tables.
So, first, to clarify, let's rename our models:
CompanystaysCompanyProductbeesProductTypeCompany_ProductbeesProductPersonstaysPersonCompany_Product_PersonbeesPurchase
And then we see that:
- A
Producthas oneCompanyand oneProductType. Conversely, the sameCompanycan be related to multipleProductand the sameProductTypecan be related to multipleProduct. - A
Purchasehas oneProductand onePerson. Conversely, the sameProductcan be related to multiplePurchaseand the sameProductcan be related to multiplePerson.
Note that there are no many-to-many relationships anymore. The relations bee:
Company.hasMany(Product, { foreignKey: "panyId" });
Product.belongsTo(Company, { foreignKey: "panyId" });
ProductType.hasMany(Product, { foreignKey: "productTypeId" });
Product.belongsTo(ProductType, { foreignKey: "productTypeId" });
Product.hasMany(Purchase, { foreignKey: "productId" });
Purchase.belongsTo(Product, { foreignKey: "productId" });
Person.hasMany(Purchase, { foreignKey: "personId" });
Purchase.belongsTo(Person, { foreignKey: "personId" });
And then, the old pany.addProduct(product); bees
Product.create({
panyId: pany.id
productTypeId: productType.id,
price: 99
})
And analogously pany_product.addPerson(person); bees
Purchase.create({
productId: product.id,
personId: person.id,
thoughts: "nice"
})
And now, we can easily see the way to make the desired query:
Company.findAll({
include: [{
model: Product,
include: [{
model: ProductType
}, {
model: Purchase,
include: [{
model: Person
}]
}]
}]
})
The result of the above query is not 100% equivalent to the "goal" mentioned in the question, because the nesting order of Product and ProductType is swapped (and so is Person and Purchase), but converting to the desired structure is now simply a matter of writing some javascript logic, and no longer a problem involving databases or sequelize.
Conclusion
Although the database scheme provided in the question is not technically wrong per se, the solution was found by changing the scheme a little bit.
Instead of using junction tables that were more than simple junction tables, we got rid of the many-to-many relationships and "promoted" the junction tables to full-fledged entities of our scheme. In fact, the tables are the same; the changes were only in the relations and in the way to look at them.