sequelize: Many-to-many "through" table with multiple rows of identical foreign key pairs only returns one result
I’m using the mysql dialect with sequelize@^2.0.5.
My database is set up such that I have users and items. Users can mark items as “owned”, and for this I have an ownership table. My scenario allows for users to mark the same item as owned multiple times while including some additional data — for this example, a note — about one of the ownerships.
Here’s an example of the ownership table:
| id | userId | itemId | notes |
|---|---|---|---|
| 1 | 50 | 941 | This is my green one. |
| 2 | 50 | 941 | This is my blue one. |
My models are defined as follows:
var User = sequelize.define('user', {
username: DataTypes.STRING
});
var Item = sequelize.define('item', {
title: DataTypes.STRING
});
var Ownership = sequelize.define('ownership', {
id: {
type: DataTypes.INTEGER,
primaryKey: true
},
notes: DataTypes.TEXT
});
Item.belongsToMany(User, { through: Ownership });
User.belongsToMany(Item, { through: Ownership });
I am explicitly defining the id primary key on the Ownership model because of this paragraph in the Sequelize documentation:
Relations/Associations -> Belongs-To-Many Associations
By default the code above will add ProjectId and UserId to the UserProjects table, and remove any previsouly defined primary key attribute - the table will be uniquely identified by the combination of the keys of the two tables, and there is no reason to have other PK columns. To enforce a primary key on the UserProjects model you can add it manually.
When I fetch a user, I would like both of of the Items for the User to be available in the model, however only one is. The SQL that is output in the console is correct and does in fact return two results, but I think something internally in Sequelize is removing the other — perhaps looking at the foreign keys to determine the uniqueness of the Item. Based on the paragraph in the documentation, I assumed that adding the id attribute to the Ownership model would fix this and treat each one uniquely.
User.find({
where: { id: 50 },
include: [{ all: true, nested: true }]
}).then(function(user) {
console.log(JSON.stringify(user));
});
Expected:
{
"id": 50,
"username": "john",
"items": [{
"id": 1,
"title": "foobar",
"ownership": {
"id": 1,
"userId": 50,
"itemId": 941,
"notes": "This is my green one."
}
}, {
"id": 1,
"title": "foobar",
"ownership": {
"id": 2,
"userId": 50,
"itemId": 941,
"notes": "This is my blue one."
}
}]
}
Actual:
{
"id": 50,
"username": "john",
"items": [{
"id": 1,
"title": "foobar",
"ownership": {
"id": 2,
"userId": 50,
"itemId": 941,
"notes": "This is my blue one."
}
}]
}
I hope this all makes sense, and I very much appreciate any help or direction if I am doing something wrong.
About this issue
- Original URL
- State: open
- Created 9 years ago
- Reactions: 22
- Comments: 66 (9 by maintainers)
Dear bot this issue is still there. 😃
Yes this is issue is very relevant. I honestly think it is one of the biggest drawbacks currently.
2019?
Any update for 2018?
With the help of our dear friend Google, I’ve come to this issue. And sadly seen that it’s not fixed yet.
I am under the impression that this can already be done in a clean way in Sequelize. I will make some tests later. If not, I will take this.
is there any update on this? Unfortunately it is still the case and frankly quite a big drawback IMHO. Can you @mickhansen maybe at least point into the right direction whether this is theoretically possible from your point of view. then maybe i could have a look into the code to see what can be done about this.
The problem is that i really want to preserve the relation and just adding another hasMany relation would mean that i loose the context of the target Table. Or in this example above, i would like to still iterate over all items and THEN look at the ownerships.
Any update on this?
Would love to know if there’s a cleaner workaround.
Nope, feel free to submit a PR that fixes this issue. We’re happy to help with that if you need assistance when working on it
Just found this issue and im really disappointed to find out that it’s not fixed 😦
I guess the best solution is to use mongo + mongoose 😦
@sliterok That’s absolutely wrong.
Choosing DBMS model is not about features of some ORM.
It’s about data model, about problem specifics, about technical specifics. ORM is just abstraction layer and helpful library, that sometimes helps you to work with relation DB.
So, about the issue and solution…
If you need M:M with duplicates and complex join table, just think about it as about two 1:M relations.
Instead of
A belongsToMany B throw Cyou haveA hasMany CandC belongsTo B. It gives you full control (with a bit more complex queries).I think that issue represents the main point, why sequelize is awful orm. If it wasn’t an issue, than we could use db schema like this.
But instead, sequelize urge you to use db schema like this instead. Which will increase amount of data in database.
Is there any news about this? I mean this is really basic stuff we are talking about and it is not yet handled even though latest version is 4.28. @mickhansen
EDIT: This problem was reported several times already and based on that I think that really is a big issue. Also this was reported almost 3 years ago…
It never will be added/fixed.
Yeah, that’s why I’m using mongoose
I settled too with the option to actually do multiple queries. Not nice and i still think this is a big drawback. 😦
What are some good alternative approaches to this, I have the same issue? My current thinking is to make a new table that (to follow the example) stores userId, itemId and note, set each to unique: true, then running inserts and queries directly on this table and not using any defined relationship.
We don’t support duplicate entries for through tables currently. You’ll have to manually setup associations to and from ownership and manually include it for it to work currently.
@matthieu-alc Yeah, but then every time you query it you have to define the whole nested include. 6 years and counting on this issue, just saying…
Still no solution?
Yeah, there’s 11 pages of open issues that have type: bug
I am using sequelize 6.28 and surprisingly, this is how you solve it: Assuming the model Foo has a many to many relationship with model Bar, so probably we have something like that:
Foo.belongsToMany(Bar, { through: 'Views', foreignKey: 'fooId', as: 'View' })and
Bar.belongsToMany(Foo, { through: 'Views', foreignKey: 'barId', as: 'View' })Referring to the mixin/special methods, we can add a relation between those 2 models by using this function:
Foo.addBar(barId)This code will create a relationship but it will fail when you try to add the relationship again. All what you have to do is to convert the barId to a string.
Foo.addBar(String(barId))I should dive into Sequelize code and follow up what is going on there
I just ran into this bug and added this options to the main query:
Then you just merge the query.
It’s a workaround, but might help someone.
Encountered this issue today. Surprise to see its not fixed yet even after 6 years.
EDIT: This solution works for me: https://stackoverflow.com/a/58439584/11806970
After a lot of research I have finnaly been brought here…
Now is too late to change this lib in my project so I had to accept the possibility of doing 2 queries.
For newer projects I’m will using KNEX.
that does not make sense, but i guess that was meant sarcastically. if not, just let me say that you are comparing a normalized schema with a nosql database in which non normalized schemas are more or less the way to go.
you can already mimick the mongo behaviour by using the json field type in sequelize and just put everything there.
back to this ticket, funny to see it is still open. still a big issue, but well i can understand that it is hard to work on this library so… i’m just waiting 😉
still no solution?