CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Ricardo Machado
Ricardo Machado

Reputation: 782

Sequelize Many-to-many table with identical foreign key is selecting just one value

I have the following structure:

var User = sequelize.define('user', {
  name: DataTypes.STRING
});

var Post = sequelize.define('post', {
  text: DataTypes.STRING
});

var PostComment = sequelize.define('postComment ', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true
  },
  comment: DataTypes.TEXT
});

Post.belongsToMany(User, {as: 'postUserComment', through: {model: models.PostComment, unique: false}, foreignKey: 'idPost'});

User.belongsToMany(Post, {through: {model: models.PostComment, unique: false}, foreignKey: 'idUserComment'});

I am able to create multiples comments to the same post with an user.

But if i have more then one comment to the same post with the same user and try to select them by doing:

 Post.findAll({
          include: [{model: models.User, as: 'postUserComment', attributes:['name'], through: {attributes: ['comment']}},
          limit: 10,
          offset: 0,
          order: "id DESC"
...

it just return 1 comment for each user in a post. What do i have to do to select them all?

Dialect: mysql, Sequelize version: ~3.27.0

Upvotes: 1

Views: 905

Answers (1)

Ivan Drinchev
Ivan Drinchev

Reputation: 19581

Having association with BelongsToMany and the same ids is somehow tricky in Sequelize.

As you have already noticed in GitHub #6906 and other related issues there, the best way to do this is to mitigate it with different relations.

For example you can add :

Post.hasMany( models.PostComment, { foreignKey: 'idPost' } );

And then to your query 

Post.findAll({
      include: [
        {model: models.User, as: 'postUserComment', attributes:['name'], through: {attributes: ['comment']}},
        {model : models.PostComment}
      ],
      limit: 10,
      offset: 0,
      order: "id DESC"
      ..

This will not change your database structure and will have the effect that you want.

Upvotes: 2

Related Questions