CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
oderfla
oderfla

Reputation: 1797

How to associate tables in sequelize

I have this function that will need to query 3 different tables. The tables are the following:

DelegateUserLink

id
DelegateId (int)
UserId (int)

Delegate

id (int)
createdBy (int)

User

id (int)
name (varchar)

I begun by creating the association in this way:

models.DelegateUserLink.belongsTo(models.Delegate);
models.Delegate.belongsTo(models.User, {through: models.Delegate.createdBy});

I run then the query that looks this way:

return models.DelegateUserLink.findAll({
        where: { UserId: id }
        ,include: {model: models.Delegate,attributes: ['createdBy'],include: {model: models.User,attributes: []}}
    }).then(function(rs) {
        rs.forEach(function(result) {
            console.log("result:",result);
        });
        return rs;
    });

Executing this query gives this error:

SequelizeDatabaseError: column Delegate.UserId does not exist

And looking at the generated query I see this:

SELECT "DelegateUserLink"."createdAt", "DelegateUserLink"."updatedAt", "DelegateUserLink"."DelegateId", "DelegateUserLink"."UserId", "Delegate"."id" AS "Delegate.id", "Delegate"."createdBy" AS "Delegate.createdBy" FROM "DelegateUserLinks" AS "DelegateUserLink" LEFT OUTER JOIN "Delegates" AS "Delegate" ON "DelegateUserLink"."DelegateId" = "Delegate"."id" LEFT OUTER JOIN "Users" AS "Delegate->User" ON "Delegate"."UserId" = "Delegate->User"."id" WHERE "DelegateUserLink"."UserId" = 677;

My idea was to tell sequelize which field to use when joining tables Delegate and User by using this line 

models.Delegate.belongsTo(models.User, {through: models.Delegate.createdBy});

Where I specified "through" which field the join should be done. But apparently Im wrong. Because it tries to use Delegate.UserId which doesn't exist (and gives the error). How can I specify what field use when joining?

Upvotes: 0

Views: 1118

Answers (1)

oderfla
oderfla

Reputation: 1797

Ok, it was about using the keyword "foreignKey", like this:

models.Delegate.belongsTo(models.User, {foreignKey: 'createdBy'});

Upvotes: 1

Related Questions