FindAll 包含涉及复杂的多对(多对多)关系(sequelizejs)

2022-01-19 00:00:00 many-to-many node.js javascript eager-loading sequelize.js

这有Software Engineering SE 中的同级问题.

考虑 Company、Product 和 Person.

Company 和 Product 之间存在多对多关系，通过联结表 Company_Product，因为给定的公司可能会生产多个公司可以生产不止一种产品(例如汽车"和自行车")，但也可以生产给定产品，例如汽车".在联结表 Company_Product 中有一个额外的字段price"，它是给定公司销售给定产品的价格.

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.是的，这是一种多对多关系，涉及一个已经是联结表的实体.这是因为一个人可以拥有多个产品，例如 company1 的汽车和 company2 的自行车，而同一个 company_product 可以由多个人拥有，因为例如 person1 和 person2 都可能从公司1.在联结表 Company_Product_Person 中有一个额外的字段想法"，其中包含该人在购买 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.

我想使用 sequelize 进行查询，以从数据库中获取 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.

我不知道该怎么做.

为了对此进行调查，我尽可能缩短了代码.看起来很大，但大部分是模型声明样板:(要运行它，首先要执行 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.

推荐答案

OP here.

解决方案的关键是重新考虑关联.将关联更改为:

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
  • Company stays Company
  • Product becomes ProductType
  • Company_Product becomes Product
  • Person stays Person
  • Company_Product_Person becomes Purchase

然后我们看到:

  • 一个Product 有一个Company 和一个ProductType.反之，同一个Company可以关联多个Product，同一个ProductType可以关联多个Product.
  • 一个Purchase 有一个Product 和一个Person.反之，同一个Product可以关联多个Purchase，同一个Product可以关联多个Person.
  • A Product has one Company and one ProductType. Conversely, the same Company can be related to multiple Product and the same ProductType can be related to multiple Product.
  • A Purchase has one Product and one Person. Conversely, the same Product can be related to multiple Purchase and the same Product can be related to multiple Person.

请注意，不再存在多对多关系.关系变为:

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 逻辑，不再是涉及数据库或 sequelize 的问题.

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.

相关文章