FindAll with includes involving a complicated many-to-(many-to-many) relationship (sequelizejs)

Question

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…

Accepted Answer

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 provi…

FindAll with includes involving a complicated many-to-(many-to-many) relationship (sequelizejs)

Solution

Problem