FindAll with包含涉及复杂的多对多(多对多)关系(sequelizejs) [英] FindAll with includes involving a complicated many-to-(many-to-many) relationship (sequelizejs)
问题描述
这有软件工程SE中的同级问题.
考虑Company，Product和Person.
通过联结表Company_Product在Company和Product之间存在多对多关系，因为给定的公司可能生产多个产品(例如汽车"和自行车") ，也可以由多家公司生产给定的产品(例如汽车").在联结表Company_Product中，有一个额外的价格"字段，它是给定公司出售给定产品的价格.
There is a many-to-many relationship between Company and Product, through a junction table Company_Product, because a given company may produce more than one product (such as "car" and "bicycle"), but also a given product, such as "car", can be produced by multiple companies. In the junction table Company_Product there is an extra field "price" which is the price in which the given company sells the given product.
通过连接表Company_Product_Person在Company_Product和Person之间存在另一对多关系.是的，这是一个涉及一个实体的多对多关系，该实体已经是一个联结表.这是因为一个人可以拥有多个产品，例如，公司1的汽车和公司2的自行车，而同一公司产品又可以由一个以上的人拥有，因为例如person1和person2都可以从公司1.在联结表Company_Product_Person中，有一个额外的字段"thoughts"，其中包含该人在购买company_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 company1 and a bicycle from company2, and in turn the same company_product can be owned by more than one person, since for example both person1 and person2 could have bought a car from company1. 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 company_product.
我想用续集进行查询，以从数据库中获取Company的所有实例，以及与所有相关的Products和相应的Company_Product，依次又包括与所有相关的Persons和相应的Company_Product_Persons.
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.
我尽可能地缩短了代码以进行调查. 看起来很大，但其中大多数是模型声明样板:(要运行它，请先执行npm install sequelize sqlite3)
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("company", {
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("company_product", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
companyId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "company",
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("company_product_person", {
id: {
type: Sequelize.INTEGER,
allowNull: false,
autoIncrement: true,
primaryKey: true
},
companyProductId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: "company_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: "company_product", foreignKey: "companyId", onDelete: "CASCADE" });
Product.belongsToMany(Company, { through: "company_product", foreignKey: "productId", onDelete: "CASCADE" });
// Many to Many relationship between Company_Product and Person
Company_Product.belongsToMany(Person, { through: "company_product_person", foreignKey: "companyProductId", onDelete: "CASCADE" });
Person.belongsToMany(Company_Product, { through: "company_product_person", foreignKey: "personId", onDelete: "CASCADE" });
// ================= TEST =================
var company, product, person, company_product, company_product_person;
sequelize.sync({ force: true })
.then(() => {
// Create one company, one product and one person for tests.
return Promise.all([
Company.create({ name: "Company test" }).then(created => { company = created }),
Product.create({ name: "Product test" }).then(created => { product = created }),
Person.create({ name: "Person test" }).then(created => { person = created }),
]);
})
.then(() => {
// company produces product
return company.addProduct(product);
})
.then(() => {
// Get the company_product for tests
return Company_Product.findAll().then(found => { company_product = found[0] });
})
.then(() => {
// person owns company_product
return company_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(companies => {
console.log(JSON.stringify(companies.map(company => company.toJSON()), null, 4));
});
})
.then(() => {
// And I can get the list of Company_Products with their Persons...
return Company_Product.findAll({
include: [{
model: Person
}]
}).then(companyproducts => {
console.log(JSON.stringify(companyproducts.map(companyproduct => companyproduct.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(companies => {
console.log(JSON.stringify(companies.map(company => company.toJSON()), null, 4));
});
});
我的目标是一次性获取具有所有深层嵌套的Persons和Company_Product_Persons的Companys数组:
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": "...",
"company_product": {
"id": 1,
"companyId": 1,
"productId": 1,
"price": null,
"createdAt": "...",
"updatedAt": "...",
"persons": [
{
"id": 1,
"name": "Person test",
"createdAt": "...",
"updatedAt": "...",
"company_product_person": {
"id": 1,
"companyProductId": 1,
"personId": 1,
"thoughts": null,
"createdAt": "...",
"updatedAt": "..."
}
}
]
}
}
]
}
];
我该怎么做?
注意:我可以分别进行两个查询，并编写一些代码以联接"检索到的对象，但这将在计算上昂贵且丑陋.我正在寻找执行此操作的正确方法.
Note: I could make both queries separately and write some code to "join" the retrieved objects, but that would be computationally expensive and ugly. I am looking for the right way to do this.
推荐答案
在这里运行.
解决方案的关键是重新考虑关联.将关联更改为:
The key to the solution is to rethink the associations. Change the associations to:
Company.hasMany(Company_Product, { foreignKey: "companyId" });
Company_Product.belongsTo(Company, { foreignKey: "companyId" });
Product.hasMany(Company_Product, { foreignKey: "productId" });
Company_Product.belongsTo(Product, { foreignKey: "productId" });
Company_Product.hasMany(Company_Product_Person, { foreignKey: "companyProductId" });
Company_Product_Person.belongsTo(Company_Product, { foreignKey: "companyProductId" });
Person.hasMany(Company_Product_Person, { foreignKey: "personId" });
Company_Product_Person.belongsTo(Person, { foreignKey: "personId" });
将return company.addProduct(product);更改为
return Company_Product.create({
companyId: company.id,
productId: product.id,
price: 99
}).then(created => { company_product = created });
将return company_product.addPerson(person)更改为
return Company_Product_Person.create({
companyProductId: company_product.id,
personId: person.id,
thoughts: "nice"
}).then(created => { company_product_person = created });
回答问题的查询是
Company.findAll({
include: [{
model: Company_Product,
include: [{
model: Product
}, {
model: Company_Product_Person,
include: [{
model: Person
}]
}]
}]
})
最终的JSON结构不完全是所提到的目标"，而只是重新排序的问题.
The resulting JSON structure is not exactly the "goal" mentioned in question but it's just a matter of re-ordering.
我找到了一个解决方案，该解决方案涉及重新整理表之间的关联，即使问题中给出的关联在技术上没有错.一种寻找问题的新方法，即更改关联，是找到一种实现我想要的方法的关键.
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.
首先，我的问题中给出的两个联结表都不仅仅是只是"联结表.它们不仅是定义哪些元素与哪些元素相关的工具，还更多:
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);
第一个Company_Product也与其他表本身有关系.
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 becomes very simple.
当我们看到我们正在建模可以购买的物品以及自己购买的物品时，解决方案就会出现.与其将这些信息伪装"在多对多关系的联结表中，不如将它们作为具有自己的表的显式实体作为方案中的显式实体.
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:
-
Company保持Company -
Product变为ProductType -
Company_Product变为Product -
Person保持Person -
Company_Product_Person变为Purchase
CompanystaysCompanyProductbecomesProductTypeCompany_ProductbecomesProductPersonstaysPersonCompany_Product_PersonbecomesPurchase
然后我们看到:
- 一个
Product具有一个Company和一个ProductType.相反，同一Company可以与多个Product相关，而同一ProductType可以与多个Product相关. - 一个
Purchase具有一个Product和一个Person.相反，同一Product可以与多个Purchase相关，而同一Product可以与多个Person相关.
- 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 become:
Company.hasMany(Product, { foreignKey: "companyId" });
Product.belongsTo(Company, { foreignKey: "companyId" });
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" });
然后，旧的company.addProduct(product);变为
Product.create({
companyId: company.id
productTypeId: productType.id,
price: 99
})
类似地，company_product.addPerson(person);变为
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
}]
}]
}]
})
上述查询的结果不是100％等同于问题中提到的目标"，因为交换了Product和ProductType的嵌套顺序(Person和Purchase也是如此)，但是转换为所需的结构是现在只需编写一些JavaScript逻辑，而不再是涉及数据库或续集的问题.
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.
尽管问题中提供的数据库方案本身在技术上并不是错误的，但通过稍微更改方案即可找到解决方案.
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.
这篇关于FindAll with包含涉及复杂的多对多(多对多)关系(sequelizejs)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
