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?
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 !
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
andCountry
and trying to keep the extra properties in the junction table, you should have three tables:Person
,Country
andVisit
. AVisit
is a full entity by itself, and there is a one-to-many relationship betweenPerson
andVisit
(the can be many visits regarding the same person) and also a one-to-many relationship betweenCountry
andVisit
. 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()
(inVisit
) and.belongsTo()
(inPerson
andCountry
).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.