Removing primary key constraints for belongsToMany

[Ryan Poole]

Hi,

Using a belongsToMany relationship creates primary key constraints for both source and destination foreign keys, presumably to prevent duplicate entries. However, I have a scenario in which I require duplicates in this joining table. 

My question is, is there an option on the belongsToMany to disable these primary key constraints?

[Andrew Eddie]

I've got a similar problem. Is it possible to redefine the primary key of a through table to include another field. My through table has these attributes:

{

entityId: {

type: Sequelize.INTEGER,

primaryKey: true

},

relationship: {

type: Sequelize.STRING,

allowNull: false,

primaryKey: true

},

targetId: {

type: Sequelize.INTEGER,

primaryKey: true

}

}

As you can see, I want a compound primary key so I can get data in the through table like this:

1 | likes      | 2

1 | manager-of | 2

Is this possible to achieve?

Thanks in advance.

Regards,

Andrew Eddie

[Andrew Eddie]

Worked it out (isn't that always the way)

var EntityRelationship = sequelize.define('entityRelationship', attributes, {

classMethods: {

associate: function (models) {

models.entity.belongsToMany(models.entity, {

through: {

model: EntityRelationship,

unique: false

},

as: 'Relationship',

foreignKey: 'entityId',

otherKey: 'targetId'

});

}

}

});

The through.unique flag needs to be set as false.

Regards,

Andrew Eddie

[Andrew Eddie]

Stuck again, sorry.

In the docs there is this example:

User = sequelize.define('user', {})
Project = sequelize.define('project', {})
UserProjects = sequelize.define('userProjects', {
    status: DataTypes.STRING
})
 
User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })

How can I craft a query in to get the associations for a given value of "status". It does not appear that getAssociations mixin support additional where conditions.

[Mick Hansen]

You can try using {through: {where: {}}, but it might only work on includes

--

Mick Hansen

@mhansendev
mhansen.io

[Andrew Eddie]

Ok, I’ve confirmed that simple User-Project examples from the docs work as expected. What seems to be a problem, however, is a self-referencing belongs-to-many relationship. Using the Person-Children example from the tests I tested this script:

var Sequelize = require('sequelize');

var db = new Sequelize('', '', '', {
   host: 'localhost',
   dialect: 'sqlite',
   storage: ':memory:'
});

Person = db.define('Person', {
   name: Sequelize.STRING
});
PersonChildren = db.define('PersonChildren', {
   relationship: Sequelize.STRING
});

var Children = Person.belongsToMany(Person, {
   as: 'Children',
   through: PersonChildren
});

db.sync()
   .then(function () {
      return Promise.all([
            Person.create({ name: 'John' }),
            Person.create({ name: 'Mary' }),
            Person.create({ name: 'Greg' })
         ])
         .then(function (results) {
            var john = results[0];
            var mary = results[1];
            var greg = results[2];

            console.log('John:', john.get());
            console.log('Mary:', mary.get());

            return Promise.all([
               john.addChildren(mary, {
                  relationship: 'is father of'
               }),
               john.addChildren(greg, {
                  relationship: 'is step-father to'
               })
            ]);
         });
   })
   .then(function () {
      return Person.findAll({
            where: {
               name: 'John'
            },
            include: [
               {
                  model: Person,
                  through: {
                     where: {
                        status: 'is father of'
                     }
                  }
               }
            ]
         })
         .then(function (results) {
            results.forEach(function (result) {
               console.log(result.get());
            });
         });
   })
   .catch(console.stack);

But this leads to this error:

Unhandled rejection Error: Person is not associated to Person!
    at validateIncludedElement (/Users/martialeagle/git/mumba/odyssey-backend-leave/node_modules/sequelize/lib/model.js:569:11)

Any thoughts on how to get that to work properly?

[Mick Hansen]

If you have an alias on an association you need to reuse that in an include so we can find the proper association:
model: Person => model: Person, as: 'Children'

[Andrew Eddie]

Ahha, I had wondered about how that works. Thanks - that fixes it.

Regards,
Andrew Eddie