CODEWITHSUNDEEP
node.jsormsequelize.js
Philipp
Philipp

Reputation: 23

Sequelize - How to get entries from one table that are not assiciated by another table

I used Sequelize to define three Entities for users rating posts:

var User = sequelize.define('User', {
  email:  {
    type: Sequelize.STRING,
    primaryKey: true
  }
});

var Post = sequelize.define('Post', {
  link: {
    type: Sequelize.STRING,
    primaryKey: true
  }
});

var Rating = sequelize.define('Rating', {
  result: Sequelize.STRING
});

Rating.belongsTo(Post);
Post.hasMany(Rating);

Rating.belongsTo(User);
User.hasMany(Rating);

A user can rate several posts. Each rating belongs to exactly one user and one post.

Now I'd like to query for a given user all posts that are not already rated by this user. I tried a thousand ways but without success. Any idea how to achieve this in Sequelize? Thanks a lot!

Upvotes: 2

Views: 3468

Answers (1)

Rohit Dalal
Rohit Dalal

Reputation: 866

There are two methods either use raw query in Sequelize or via Sequelize query as well -

Raw - 

return Db.sequelize.query("SELECT * FROM Post P WHERE (P.id NOT IN (SELECT postId FROM Ratings R WHERE R.userId="+userId+")) ",{ type: Sequelize.QueryTypes.SELECT });

Sequelize - 

return Post.findAll({
where: {
Sequelize.literal("(posts.id NOT IN (SELECT R.postId FROM Rating R WHERE R.userId="+userId+"))")
}
});

Upvotes: 6

Related Questions