How to build EXISTS clause in sequelize

Question

I have a model where there are 3 entities:

User Project Contributor 

A Project has a Owner (who is a User) and has multiple Contributors (who are Users). In my dashboard, I want to list a User's projects i.e. all Projects where the User is a Owner or a Contributor.

Finding owned projects using Project.findAll({where: [owner: user]}) is trivial. How do I modify this to find out the Projects where the user is also a contributor? This would (in the database query) translate to a sub-query using EXISTS clause.

Answer 1

In general you can use exists clause in where like this:

Project.findAll({     where: {          $and:[sequelize.literal('exists (select 1 from "someTable" where ...)']     } }) 

Answer 2

For example we define next associations:

Project.belongsToMany( User, {through: 'Contributor'} ); User.belongsToMany( Project, {through: 'Contributor'} );  Project.belongsTo( User, { as: 'Owner', foreignKey: 'owner'} ); 

If you want to find projects where the user is a contributor and the owner at the same time you can do it like this:

user.getProjects({ where: {owner: user.id} });