hasMany through - filter/where/order not working

[fabien]

var Space = app.loopback.getModel('Space');
var User = app.loopback.getModel('User');
var Account = app.loopback.getModel('Account');

Space.hasAndBelongsToMany('users', { through: Account });
Space.hasMany('accounts');

User.hasAndBelongsToMany(Space, { as: 'spaces', through: Account });
User.hasMany('accounts');

Example: /api/spaces/client-a/users?filter[where][username]=toby // result: []
Example: /api/spaces/client-a/users?filter[order]=username // result: unordered array

Regarding sorting, since models are fetched individually (byId vs. find), ordering is not applied either. This might very well be a limitation of how juggler handles relations at the moment (very flexible regarding mixed datasources, but not optimal performance wise).

I think the best approach would be to gather the related ids from the through-model first, then issue a second query on the actual target model, scoped to the previously retrieved ids - this is not only more efficient, but should enable the filtering capabilities as expected.

[lchenay]

When using hasAndBelongsToMany of hasManyThrought and making a find on relation (with api or with model) filter apply to the throught model and not on the destination model.

This is a huge problem.

[bajtos]

Possibly related: #94 #85

[mrjrdnthms]

+1

[glesage]

+1 indeed! (to @lchenay 's comment) - and I'm interested in both hasAndBelongsToMany and hasManyThrought, just fyi

Thanks!

[janober]

+1 also did just run into the same issue with hasAndBelongsToMany. Wanted to use "fields".

[glesage]

FYI, for now I'm JSON stringifying and then JSON parsing the results of my queries... very inneficient but works:

MyModel.find({include: "my_relation"}, function (err, myModels)
 {
    myModels = JSON.parse(JSON.stringify(myModels));
    // then when you call an individual result's.my_relation you actually get what you want
}

[superkhau]

When using hasAndBelongsToMany of hasManyThrought and making a find on relation (with api or with model) filter apply to the throught model and not on the destination model.

Possible solution at #476

[superkhau]

@raymondfeng Is this related to strongloop/loopback/issues/1076?

[raymondfeng]

@superkhau I think so. Can you verify?

[superkhau]

.../api/spaces/client-a/users?filter[where][username]=toby // result: []

@fabien What is client-a in this example?

[jannyHou]

Hi @raymondfeng, the fix is published in 3.x and I am not sure should I backport it to 2.x?
I feel the filter would be good to have consistent behaviour in 2.x and 3.x, but the change itself is a bit breaking lol.
Any idea?

[raymondfeng]

@jannyHou What are the breaking changes? How much effort is needed to backport to 2.x?

[kristojorg]

I believe this may be still broken in 3.x . I have a TopUser extended from User which hasMany Role, but the response of GET on /TopUser/:id/roles does not show any roles.

[jannyHou]

@kristojorg could you fork https://github.com/strongloop/loopback-sandbox and provide me your code? Thanks.
From your description, it's hard to tell is it broken by this fix or some other factors, for example, this issue is specific for relation hasManyThrough not hasMany, and some connector could also affect the behavior.
With your sample app I can work with you to figure out how to get roles for a topuser.

[ilkerc]

+1

[sidewaiise]

Wow this is a big problem. {where} filter does not map to target model. I'd like to fix it in a fork.

Have looked at the libraries - wouldn't it make sense to have RelationDefinition.hasManyThrough() instead of trying to cram the through into hasMany? Seems it would be easier to manage from a library perspective.

Also please confirm - is the problem persisting in hasAndBelongsToMany relation? We did not use that because our datasource couldn't find the correct join table and/or relation labelings.

[jannyHou]

The fix is already released in 2.55.0 and also released long time ago in [email protected], otherwise tests won't pass:
https://github.com/strongloop/loopback-datasource-juggler/blob/master/test/relations.test.js#L726

Could anyone provide me a sandbox that reproduce the problem? see #166 (comment)

[ilkerc]

@jannyHou I tried. Couldn't reproduce. I'm using Mongo ObjectId's in every id field including reference id fields in relational tables. I think it's somehow related to this. If I catch anything related with mongo connector, shall I continue reporting here ?

[jannyHou]

Thank you @ilkerc, if it's a mongodb specific problem, then https://github.com/strongloop/loopback-connector-mongodb would be the best place to open issue and discuss.

[mbenedettini]

@jannyHou as far as I can see, those tests you mentioned only test the case when filter is an id. If I change the where condition to be, for example, name or any other attribute, then the query stops working.

[jannyHou]

@mbenedettini Thank you I think it's caused by same reason as issue strongloop/loopback#3487 reported.
And there is a PR fix opened for it #1406

I am going to close this issue since the original bug is fixed in [email protected], the failures you observe is caused by another bug.
The conversation happens here is a little misleading :( I find some similar issues opened for new bug in loopback and loopback-datasource-juggler, will post another story to track the new bug&fix in the comment later.
Thanks for understanding.

[jannyHou]

Let's keep discussion in issue strongloop/loopback#3487, which should reflect the filter issue you run into recently.