Sequelize: Many-to-many relationship with property on the edge/association itself

Created on 8 Mar 2018  路  3Comments  路  Source: sequelize/sequelize

The "n:m" example on http://docs.sequelizejs.com/manual/tutorial/associations.html with Post and Tag is clear and I understand that PostA can be associated with TagA, TagB and TagC at the same time as TagA can be associated to PostA, PostB, PostC. ie. a normal many-to-many relationship.

But as I understand PostA cannot be associated twice to the same tag, ie. PostA cannot be associated to TagA and then again be associated with the same TagA ?

In that case it make no sense but consider an example of Person and Country with a many-to-many relationship but where we on the relationship itself store a date of when that person visited that country.

ie.

Anna visited France the 14 of july
Anna visited Sweden the 12 of june
Anna visited Sweden the 23 of august

When I try to create this kind of relationship using the Post and Tag example and read a person using something like Person.findAll({include: [ Country ]}) I only get back one association between Anna and Sweden, not two as I would expect.

  • Is it correct that you cannot do this kind of many-to-many association in Sequelize = feature request?

  • Or is it possible = request to add an appropriate example to the documentation?

Most helpful comment

Hello, I am not a Sequelize developer, but I think I can help you. Instead of asking whether Sequelize does or does not support the behavior you want, I think you should change how you're attacking your problem.

Junction tables, the tables that exist in relational databases to represent many-to-many relationships, initially have only two fields (the foreign keys of each table defining the many-to-many relationship). While it's true that it's possible to define extra fields/properties on that table, i.e. extra properties for the association itself (as you put in the issue title), care should be taken here: if it's getting overcomplicated, it's a sign that you should "promote" your junction table to a full-fledged entity.

Putting more pragmatically to your use case: you should change your scheme. Instead of having a many-to-many relationship between the two tables Person and Country and trying to keep the extra properties in the junction table, you should have three tables: Person, Country and Visit. A Visit is a full entity by itself, and there is a one-to-many relationship between Person and Visit (the can be many visits regarding the same person) and also a one-to-many relationship between Country and Visit. This way, it is 100% guaranteed that Sequelize supports whichever query you want to do.

This way instead of using .belongsToMany(), you would use .hasMany() (in Visit) and .belongsTo() (in Person and Country).

By the way, not long ago I was facing a similar issue in one of my projects that I ended up solving with a very similar change in the scheme itself - see here and here.

Hopefully this helps.

All 3 comments

Hello, I am not a Sequelize developer, but I think I can help you. Instead of asking whether Sequelize does or does not support the behavior you want, I think you should change how you're attacking your problem.

Junction tables, the tables that exist in relational databases to represent many-to-many relationships, initially have only two fields (the foreign keys of each table defining the many-to-many relationship). While it's true that it's possible to define extra fields/properties on that table, i.e. extra properties for the association itself (as you put in the issue title), care should be taken here: if it's getting overcomplicated, it's a sign that you should "promote" your junction table to a full-fledged entity.

Putting more pragmatically to your use case: you should change your scheme. Instead of having a many-to-many relationship between the two tables Person and Country and trying to keep the extra properties in the junction table, you should have three tables: Person, Country and Visit. A Visit is a full entity by itself, and there is a one-to-many relationship between Person and Visit (the can be many visits regarding the same person) and also a one-to-many relationship between Country and Visit. This way, it is 100% guaranteed that Sequelize supports whichever query you want to do.

This way instead of using .belongsToMany(), you would use .hasMany() (in Visit) and .belongsTo() (in Person and Country).

By the way, not long ago I was facing a similar issue in one of my projects that I ended up solving with a very similar change in the scheme itself - see here and here.

Hopefully this helps.

I like the idea suggested by @papb , As for what OP asked this is a known bug with Sequelize https://github.com/sequelize/sequelize/issues/3493

Thanks a lot @papb for suggestion and great discussions in the links! I went for something similar. Thanks for clarification @sushantdhiman !

Was this page helpful?
0 / 5 - 0 ratings