sailsjs / waterline query "where" not empty

Question

Hy there,

Before going to the hacky / cutom way i wanted to know if there is a built in query way to check for an empty / non empty many to many relationship as i was not successfull neither on google nor the doc.

If i take the example in the doc let's imagine i want to retrive a user only if he has a a Pet or Retrive a Pet without any Owner through a query.

// A user may have many pets
var User = Waterline.Collection.extend({

  identity: 'user',
  connection: 'local-postgresql',

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

    // Add a reference to Pet
    pets: {
      collection: 'pet',
      via: 'owners',
      dominant: true
    }
  }
});

// A pet may have many owners
var Pet = Waterline.Collection.extend({

  identity: 'pet',
  connection: 'local-postgresql',

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

    // Add a reference to User
    owners: {
      collection: 'user',
      via: 'pets'
    }
  }
});

P.s. i know how to filter results after query execution that's not what i'm asking :)

Answer 1

There is nothing built in (aka User.hasPet() ) or something like that., so the simple answer is NO

If I know of these issues before hand I tend to write my DB in such a way that the queries are fast. IE: the User schema would have a hasPets column. Whenever a pet is added/removed a callbacks hits the user table to mark that field if it has an owner or not. So then I can query User.findAll({hasPet:true}).

Its a little much, but it depends on where you speed is needed.

Answer 2

This is a bit late, but I wanted to let you know it's pretty easy to do this with the Waterline ORM lifecycle functions. I've done it in a few of my projects. Basically, use the beforeCreate and beforeUpdate functions to set your flags. For your user, it might look like...

 var User = Waterline.Collection.extend({

  identity: 'user',
  connection: 'local-postgresql',

  beforeCreate: function(values, next) {
    if (values.pets) {
      values.has_pet = true;
    } else {
      values.has_pet = false;
    }
    next();
  }

  beforeUpdate: function(values, next) {
    if (values.pets) {
      values.has_pet = true;
    } else {
      values.has_pet = false;
    }
    next();
  }

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

    // Add a reference to Pet
    pets: {
      collection: 'pet',
      via: 'owners',
      dominant: true
    },

    has_pet: {
      type: 'boolean'
    }
  }
});

Then you can query based on the has_pet attribute