CODEWITHSUNDEEP
javascriptsails.jswaterline
prototype
prototype

Reputation: 3313

Sails.js query db by foreign key

I'm wondering how to make a query by foreign key using the default Waterline model.

I have two models Post and Category - Post has a foreign key category. I need to make a query like so:

Post.find({
  where: {
    category: query
  }
}).exec(function (err, data) {});

In this case query is a string so the results returned should be Posts containing searched category.

What is the best way to do this?

Note: Current example does not work

Upvotes: 1

Views: 966

Answers (2)

Andi N. Dirgantara
Andi N. Dirgantara

Reputation: 2051

Your model should be

// Post
module.exports = {
  attributes: {
    name: {
       type: 'string'
    },
    category: {
       model: 'category'
    }
  }
};

// Category
module.exports = {
  attributes: {
    name: {
       type: 'string'
    },
    post: {
       collection: 'Post',
       via: 'category'
    }
  }
};

Then query from category would be

Category
    .find()
    .where({ name: query })
    .populateAll()
    .exec(function (error, categories) {
       var catArr = [];

       if (categories.length) {
         categories.map(function (item) {
           catArr.push(item.id);
         });
       }

       Post.find().where({ category: catArr }).exec(function (error, posts) {
         // do stuff
       });

    });

Or simply you can query it from post by

Post
    .find()
    .where({ category: categoryId })
    .populateAll()
    .exec(function (error, posts) {
       // posts is all post with category that defined
    });

Make sure that you know categoryId if you want to query it from post. I usually use categoryId is string and slugify from name, so I can query category by it's name and make sure that category name (and also ID of course) is unique.

Upvotes: 3

prototype
prototype

Reputation: 3313

Figured how to implement this using the category id:

Category.find().where({ name: query }).exec(function (error, categories) {
   var catArr = [];

   if (categories.length) {
     categories.map(function (item) {
       catArr.push(item.id);
     });
   }

   Post.find().where({ category: catArr }).exec(function (error, posts) {
     // do stuff
   });

});

Also had to add attributes in the models like so:

// Post
module.exports = {
  attributes: {
    name: {
       type: 'string'
    },
    category: {
       model: 'category'
    }
  }
};

// Category
module.exports = {
  attributes: {
    name: {
       type: 'string'
    },
    post: {
       model: 'post'
    }
  }
};

Upvotes: 1

Related Questions