3

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 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.

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.

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("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));
        });
    });

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": "..."
                            }
                        }
                    ]
                }
            }
        ]
    }
];

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 computationally expensive and ugly. I am looking for the right way to do this.

Pedro A
  • 3,989
  • 3
  • 32
  • 56

1 Answers1

7

OP here.

Short answer

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" });

Change return company.addProduct(product); to

return Company_Product.create({
    companyId: company.id,
    productId: product.id,
    price: 99
}).then(created => { company_product = created });

Change return company_product.addPerson(person) to

return Company_Product_Person.create({
    companyProductId: company_product.id,
    personId: person.id,
    thoughts: "nice"
}).then(created => { company_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 becomes 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:

  • Company stays Company
  • Product becomes ProductType
  • Company_Product becomes Product
  • Person stays Person
  • Company_Product_Person becomes Purchase

And then we see that:

  • 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" });

And then, the old company.addProduct(product); becomes

Product.create({
    companyId: company.id
    productTypeId: productType.id,
    price: 99
})

And analogously company_product.addPerson(person); becomes

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.

Pedro A
  • 3,989
  • 3
  • 32
  • 56