CODEWITHSUNDEEP
sequelize.js
user3631341
user3631341

Reputation: 525

sequelize specify join key in query with include

I have two models user_auth and user_follow. Such that 2 user_follow fields (follower_id and followee_id) reference the same field (id) of user_auth.

I want to know how to specify the join criteria that will be used when I select users with the associated user_follow where he is the followee. I have this code:

userAuth.findOne({
        where:{
            id: data.viewee_id
        },
        include: [{
            model: userFollow,
            required: false,
            attributes: ["id"]
        }]
    })

Which result to a query with join clause like this:

FROM
`user_auth` AS `user_auth`
LEFT OUTER JOIN
`user_follow` AS `user_follow`
ON
`user_auth`.`id` = `user_follow`.`followee_id`

I don't know where to specify the join key. My suspect is because my user_follow was defined with:

classMethods: {
    associate: function(models) {
        userAuth.hasOne(models.user_follow, {
            onDelete: "CASCADE",
            onUpdate: "CASCADE",
            foreignKey: 'follower_id',
            targetKey: 'id',
        });

        userAuth.hasOne(models.user_follow, {            
            onDelete: "CASCADE",
            onUpdate: "CASCADE",
            foreignKey: 'followee_id',
            targetKey: 'id',
        });
    }
},

Based on practical testing, it was caused by the latter hasOne. If I delete that, the find query used follower_id as join key.

Is it possible to specify the join key in query? Because otherwise my future queries will be contrained by the model definition.

PS: I know that I can add a where key to my include but it just concatenates by AND a new join phrase to the main join.

Upvotes: 1

Views: 1906

Answers (1)

Evan Siroky
Evan Siroky

Reputation: 9418

You need to specify an alias using the as option in your association definitions. See the docs.

userAuth.hasOne(models.user_follow, {
  as: 'Follower',
  onDelete: "CASCADE",
  onUpdate: "CASCADE",
  foreignKey: 'follower_id',
  targetKey: 'id',
});

userAuth.hasOne(models.user_follow, {            
  as: "Followee",
  onDelete: "CASCADE",
  onUpdate: "CASCADE",
  foreignKey: 'followee_id',
  targetKey: 'id',
});

Upvotes: 2

Related Questions