CODEWITHSUNDEEP
javascriptnode.jsormsequelize.js
Marc Mailhot
Marc Mailhot

Reputation: 480

sequelize - Where clause with associations

In sequelize I have boards and users setup, with a many to many association as follows

User.hasMany(Board, {through: BoardUsers});
Board.hasMany(User, {through:BoardUsers});

Is there anyway that, using a where clause I can find users that belong to one of a list of boards. For example, lets says I have 3 boards and I would like to find the first 20 users (using the default sort order) who belong to either board 1 or 3. Is there a way to do this without executing separate finds for each board, then manually combining the results.

I would love to be able to do something like:

User.findAll({where:{board: [1,3]}});

but I can't seem to find a way to do this.

As far as I can tell the equivalent SQL would be something like:

SELECT * FROM `users` WHERE id IN (SELECT userID FROM boardusers WHERE boardId IN (1,3))

But I would love to be able to do it through the ORM.

Upvotes: 3

Views: 2823

Answers (2)

Joel
Joel

Reputation: 2215

Quite a late response but just had this same question, and here's how I got it to work in Sequelize 3.24.0 (something like this);

User.findAll({ include: [{ model: Board, where: { $in: [1,3] }}] });

Upvotes: 0

soulcheck
soulcheck

Reputation: 36767

While I'm not sure if you can do this directly, you can always query for Boards and eagerly fetch users.

Something along these lines:

Board.findAll({where: {id: [1,3]}}, { include: [ User ] })

Upvotes: 2

Related Questions