CODEWITHSUNDEEP
node.jsmany-to-manysails.jswaterline
holyxiaoxin
holyxiaoxin

Reputation: 710

sails.js many to many query

I am trying to build a chat app. I have a many-to-many association. A room have many users. And a user can have many rooms. I am trying to retrieve the room which has both user A (fromUserId) and user B (toUserId). I am trying something like this but I know it's not correct:

Room.find().populate('users', { where:{ id: [fromUserId, toUserId] } }).exec(function(err, rooms){
  console.log(rooms);
});

The problem here is that it returns any rooms that users.id = fromUserId or toUserId. What I need here is an and query.

Any help appreciated. (:

Upvotes: 1

Views: 1324

Answers (2)

Ryan W
Ryan W

Reputation: 6173

If you're using Mongodb with waterline, you can use $in 

Room.native(function(err, collection) {
collection.find({
    "users" : {
      $in : [fromUserId, toUserId]
    }
  }, function(err, results) {
    if (err) return res.badRequest(err);
    console.dir(results)
  });
});

the disadvantage is that its native mongodb feature, you can't use that in other database.

Upvotes: 3

sgress454
sgress454

Reputation: 24948

You'd be hard-pressed to do this even with raw SQL. Your best bet is to get all the rooms that each user is in, and then get the intersection:

// Get the fromUser and their rooms
User.findOne(fromUserId).populate('rooms').exec(function(err, fromUser) {
  // Get the toUser and their rooms
  User.findOne(toUserId).populate('rooms').exec(function(err, toUser) {
    // Get the IDs of the rooms they are both in
    var fromUserRoomIds = _.pluck(fromUser.rooms, 'id');
    var toUserRoomIds = _.pluck(toUser.rooms, 'id');
    var sharedRoomIds = _.intersection(fromUserRoomIds, toUserRoomIds);
    // Find those rooms
    Room.find({id: sharedRoomIds}).exec(...);
  });
});

You could make this more elegant using async.auto, and don't forget to handle your errors!

Upvotes: 2

Related Questions