Sails.js / Waterline: How do I use a collection attribute in a find query?

Question

I am working on a Waterline query which filters objects by a collection attribute. In this simplified example, I have two models, Video and Category:

// Video.js

module.exports = {

attributes: {
    title: {
      type: 'string'
    },
    categories: {
      collection: 'Category',
      via: 'videos'
    }
  }
};


// Category.js

module.exports = {

  attributes: {
    name: {
      type: 'string'
    },
    videos: {
      collection: 'Video',
      via 'categories'
    }
  }
};

I want to find all the videos which are associated with a certain category. I have the category id stored in a variable named categoryID and am trying this query:

Video.find('categories': categoryID).exec(function (err, videos) {
    // videos should contain all videos associated with the categoryID
});

However, I always end up with an empty result, even though there are videos associated with the category for which I am looking. I know that waterline does currently not support deep queries for values in collection attributes, but I thought at least a query for the id of the object would work. Am I wrong?

If so, is there an alternative way to achieve the desired result without using native queries?

I am aware that I could add a collection attribute to Category and build my query from the Category side. However, this is only the beginning of a more complicated search where I also narrow down the result using other attributes stored in a Video object, such as the user id of the creator of a video. In the end, I iterate through the video results using pagination. So I am looking for a way to retrieve videos of certain category which can be combined with other search attributes stored in a Video object.

score 1 · Answer 1 · answered Dec 07 '15 at 08:00

Modify Category.js to:

module.exports = {

  attributes: {
    name: {
      type: 'string'
    },
    videos: {
      collection: 'Video',
      via: 'categories'
    }
  }
};

Modify Video.js

module.exports = {

  attributes: {
    title: {
      type: 'string'
    },
    categories: {
      collection: 'Category',
      via: 'videos'
    }
  }
};

When adding a video,

var title = req.param('title');
var categories = req.param('categories').split(','); //ids of categories in format 1,3,8 etc
Video.create({name: title, categories: categories}, function(err, succ){
    if(err){
        return res.serverError(err);
    }else {
        return res.json(200, succ);
    }       
});

To find all videos with a specific category, use the populate() helper in waterline.

var categoryId = req.param('catId');
Category.find({id: categoryId}).populate('videos').exec(function(err, results){
    if(err){
        return res.serverError(err);
    }else {
        return res.json(200, results);
    }
});

Thanks for your suggestion. Unfortunately, I don't think it works for my case. I was aware that I could start my query from the category side. However, the problem is that this is just the start of a more complicated query. A video has other attributes as well, such as for example a reference to the user who created it. Now if I combine my query with other parameters which are associated with the video object, i can' start my search from the category side and just populate the video attribute. Any ideas how to address this? — Andi S., Dec 08 '15 at 03:27

Abhay · Answer 2 · 2015-12-10T10:53:15.430

1

Video model :

  module.exports = {
    attributes: {
     name: {
            type: 'string'
     },
     categories: {
            collection: 'category',
             via: 'videos'
     },
      toJSON: function() {
           var obj = this.toObject();
           return obj;
      }
   }
};

Category model :

module.exports = {

    attributes: {
          name: {
               type: 'string'
          },
   videos: {
       collection: 'Video',
       via: 'categories'
   }
}
 };

And your query will look like :

 var arr = ['56667a2cbaea1fcd11c54851','56667b1053c6c37a1283ea75'];

 Video.find().populate("categories",{id:arr}).exec(function(e, r) {
        res.json(r);    
})

edited Dec 10 '15 at 10:53

answered Dec 07 '15 at 08:10

Abhay

6,410
4
26
34

Thanks rroxysam, this is essentially the same approach that MjZac suggested. Unfortunately, it doesn't work in my case, because this is just the start of a more complicated query where I use other attributes besides category associated with a video object to narrow down the result. Any ideas how to address this? – Andi S. Dec 08 '15 at 03:30
When you linked the Video to Category, just save the catgory id in categories field, Then use the above query to get all the video data linked to the specified categories. if still, let me know. * See above modified code – Abhay Dec 08 '15 at 06:59
Thanks for the update. This would work if I had only one category per video. Unfortunately though, in my case a video can be have more than one category, so i can't change the `category` attribute in `Video` to the type `model`, it has to be a `collection`. Any ideas how to make it work with a collection attribute? – Andi S. Dec 08 '15 at 16:27
Hello Andi, the problem is that you are not associating/saving many to many association in right way. When you are creating many-to-many association in sails js, a separate collection for the interlinked ids. is created by sails. Ex. if you have video & category collection, then there will be a collection video_categories__category_videos which maps the interlinked ids, then you will be able to get the desired output, where categories & videos are via attributes in model. [Reference] (sailsjs.org/documentation/concepts/models-and-orm/associations/…) – Abhay Dec 09 '15 at 06:38
Hello rroxysam, thanks a lot for your continued help! I had tried this before, but this did not change the result. I updated it again and also changed my question to show you the edits. This still gives me an empty result though, even though the according entries can be found in 'video_categories__category_videos' in 'localDiskDb.db'. Does this work for you when you try it out? Any other ideas what I am doing wrong? – Andi S. Dec 09 '15 at 08:13
Hello Andi, You can serach all the videos related to a particular category by providing an extra field categoryId in Video model as you used earlier and then modify your Video model by adding this line : toJSON: function() { var obj = this.toObject(); return obj; } After that, test this by saving your category id in the categoryId attribute in multiple videos & then retrieve the result. Sure it will work as it works on my end. – Abhay Dec 09 '15 at 09:07
I am not sure if I understand, you mean I need another field for categoryId? Isn't that information already stored in 'categories'? As I have more than one category per video, I assume it would have to be another collection attribute? It would be great if you could post the code which works for you to clarify. Thanks! – Andi S. Dec 09 '15 at 16:17
Hi Andi, you are right, no need of extra field categoryId. I have modified the above code / query, Please have a look if its work. – Abhay Dec 10 '15 at 10:50

Sails.js / Waterline: How do I use a collection attribute in a find query?

2 Answers2