Sails: Populate with conditions

[Rafkraft]

Hi, I'm using waterline ORM in my sails.js project, (sails 0.11.4, waterline 0.10.17)

I'm trying do populate linked models with conditions (a simple INNER JOIN).
My models:

Comment.js
  attributes  : {
    post: {
      model: 'post',
      columnName: 'post_id'
    }
}

Post.js
  attributes  : {
    comments: {
      collection: 'comment',
      via: 'post'
    },
}

So it's a pretty simple has many relation. Simple populate work but I'd like to do the following things:

• Get all comments which have a post (INNER JOIN)
• Get all comments which have a post AND this post is active (INNER JOIN with condition)

I tried with these queries:

 Comment.find({}).populate('post').exec(function(err, comments){ console.log(comments) });
// I get a lot of comments with post: undefined , how to get only comments which have a post ?


Comment.find({}).populate('post', { active: true }).exec(function(err, comments){ console.log(comments.length) });
// I get exactly the same result, how to set a condition to my INNER JOIN ? 

Thank you very much :)

[particlebanana]

Hey @Rafkraft right now populate in Waterline is more like "expand this attribute". It doesn't filter the parent (inner join) at all. We are working on ideas for expanding both the criteria language and join functionality to support the various join types.

If you would like to put together a proposal following the Contribution Guide and submit it to the Roadmap of Waterline we can start to discuss the various ways this could be worked out.

[Rafkraft]

Thanks for your answer !

I'm very surprised to learn that INNER JOIN / OUTER JOIN / LEFT JOIN with or without conditions are not currently possible with waterline ORM.

Since sails+waterline aim to be a professionnal solution, I think this functionnality should be scheduled on the dev roadmap as soon as possible.

Keep on the good work !

[putudondo]

+1

[zuhrulumam]

+1

[brianh20]

Hello guys, you probably don't need this anymore but in case anyone runs into this I'll tell you how I solved a similar scenario:

Instead of doing:

modelA.find()
  .populate('modelB', condition)

I did the following. Since modelA.find returns an unpopulated modelB id (eg):

[{
 id: hash
 modelB: somehash
}
...]

I first did a modelB.find({insert condition here}) which returned a list of modelB ids I wanted to filter from the modelA find... and then I applied this filter on the find:

modelA.find({
  modelB: [ resultingArrayOfModelBIds ]
}

Bonus Track:
So in the example above you would have to say

let filterIds = [];
let activePostQuery = Post.find({where : { active: true} });

activePostQuery.exec((error, result) => {
  filterIds = result.map(elem => elem.id);
});

Comment.find({
  post: filterIds
})

[ibtesamlatif2997]

Hello guys, you probably don't need this anymore but in case anyone runs into this I'll tell you how I solved a similar scenario:

Instead of doing:

modelA.find()
  .populate('modelB', condition)

I did the following. Since modelA.find returns an unpopulated modelB id (eg):

[{
 id: hash
 modelB: somehash
}
...]

I first did a modelB.find({insert condition here}) which returned a list of modelB ids I wanted to filter from the modelA find... and then I applied this filter on the find:

modelA.find({
  modelB: [ resultingArrayOfModelBIds ]
}

Bonus Track:
So in the example above you would have to say

let filterIds = [];
let activePostQuery = Post.find({where : { active: true} });

activePostQuery.exec((error, result) => {
  filterIds = result.map(elem => elem.id);
});

Comment.find({
  post: filterIds
})

Currently waterline don't support Inner join/Right join. It only supports Left Join which is actually handled by populate as populate only returns the records of populated table if relation exists.

The solution you provided working for me. But it is too slow in my case because populate is left join and left joins are usually too slow when number of records increases on right table. But I need Inner join in my scenario and it will be efficient too.

I think Inner join functionality should be scheduled as soon as possible.

[Chibuzo]

Has this been address in Waterline yet?