Sails.js populate with where

Question

I need to select users with dogs (pets with type equal 'dog')

var User = Waterline.Collection.extend({

  identity: 'user',

  attributes: {
    firstName: 'string',
    lastName: 'string',

    pets: {
      collection: 'pet',
      via: 'owner'
    }
  }
});

var Pet = Waterline.Collection.extend({

  identity: 'pet',

  attributes: {
    type: 'string',
    name: 'string',

    owner: {
      model: 'user'
    }
  }
});

I didn't find any examples, I tried like this

User.find().populate('pets', {type: 'dog'}).exec(err, users) ...

and this

User.find().where({'pets.type': 'dog'}).populate('pets').exec(err, users) ...

but that does not work

Would be greate if result users array will has no pets records

are you going to filter users, or is your only filter ```pet.type = dog```? — Meeker, May 13 '15 at 22:41
any proposal which resolve this issue by using Waterline (not raw query) will be good, only `pet.type = dog` too — user4747674, May 13 '15 at 22:56

score 6 · Answer 1 · answered Jul 04 '16 at 18:03

6

Did you try this?

User.find().populate('pets', {
  where: {
    type: 'dog'
  }
}).exec(err, users)...

answered Jul 04 '16 at 18:03

Agustín

1,546
7
22
41

score 2 · Answer 2 · answered May 13 '15 at 23:25

2

If you don't need to query users and just need the query for dogs. You could just as easily reverse the query.

Pet.find({type: 'dog'}).populate('users').exec(err, petsWithUsers)

answered May 13 '15 at 23:25

Meeker

5,979
2
20
38

score 1 · Answer 3 · answered May 13 '15 at 22:46

What you are looking for hasn't been implemented in waterline (Sails ORM) yet, check issue #266 for more details.

User.find().populate('pets', {type: 'dog'}).exec(err, users) ...

This will return all users (User.find()) and only populate pets of type dog (populate('pets', {type: 'dog'})). So you'll have users without dogs in your results.

User.find().where({'pets.type': 'dog'}).populate('pets').exec(err, users) ...

Waterline does not support dot (.) notation. Sails-mongo does have some support for it due to MongoDB support.

Finally, if you are using one of the SQL adapters you may work around this by doing a raw sql query using .query() (docs).

Sails.js populate with where

3 Answers3

Linked