Loopback-datasource-juggler: 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 https://github.com/strongloop/loopback-datasource-juggler/pull/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?

[fabien]

@superkhau it's just an :id value

[superkhau]

Example: /api/spaces/client-a/users?filter[where][username]=toby // result: []
This issue has been resolved in strongloop/loopback#1076.

Example: /api/spaces/client-a/users?filter[order]=username // result: unordered array
This issue is still unresolved due to the way we query related models. The current implementation performs a query on the join table and sorts each returned row individually instead of sorting all the resulting rows together as a whole.

Reassigned to @raymondfeng

[maelena87]

+1 @superkhau Example: /api/spaces/client-a/users?filter[order]=username // result: unordered array
still not solved, right? still getting an unordered array.

[jannyHou]

Problems are exactly the same in both hasManyThrough and hasAndBelongsToMany
I am working on this issue and create a repo to test the filters:
https://github.com/jannyHou/loopback-sandbox/tree/has-and-belongs-to-many

I tried 6 filters:

• fields
• include
• limit
• order
• skip
  -where

order still doesn't work but other filters work properly.
I will fix order then.

---

Details of my tests:

Three models in my app: city, space, customer

1) city hasMany space & space belongsTo city
2) space hasAndBelongsToMany customer:

"relations": {
    "customers": {
      "type": "hasAndBelongsToMany",
      "model": "customer",
      "through": "spaceCustomer"
    }
  },

test cases:

• order(with problem): endpoint: GET /spaces/{id}/customers, filter: {"order": "name"}, result: unordered.
• fields: endpoint: GET /spaces/{id}/customers, filter: {"fields": {"name": false}}, result: without field name.
• include: endpoint: GET /cities/{id}/spaces, filter: {"include": {"relation":"customers"}}, result: all customers linked to space.
• limit: endpoint: GET /spaces/{id}/customers, filter: {"limit": 2}, result: only get 2 customers.
• skip: endpoint: GET /spaces/{id}/customers, filter: {"skip": 2}, result: skip first 2, only get 1 customers.
• where: endpoint: GET /spaces/{id}/customers, filter: {"where": {"name": "zoe"}}, result: only get customer zoe.

[jannyHou]

Update:
where doesn't work when filter by id

• where: endpoint: GET /spaces/{id}/customers, filter: {"where": {"id": 1}}, result: still get all customers.

I will fix it as well.

[ebarault]

it seems filter "fields" in hasManyThrough relation is still applied on through Model rather on target Model ?

[rpalacio86]

@ebarault same for "order" filter
This workaround is working for me:

 function mergeFilter(filter) {
    var mfilter = {
      include: {
        relation: "relationName",
        scope: filter || {}
      }
    };
    return mfilter;
  }
  MyModel.getRelationName = function (id, filter, cb) {
    MyModel.findById(id, mergeFilter(filter), function (err, result) {
      cb(err, result.relationName());
    });
  };

  MyModel.remoteMethod(
          'getRelationName',
          {
            description: 'Obtain relationName from MyModel.',
            accepts: [
              { arg: 'id', type: 'any', description: 'Model id', required: true,
                http: {source: 'path'}},
              { arg: 'filter', type: 'object',
                description: 'Filter defining fields and include'}
            ],
            returns: {arg: 'data', type: ['RelatedModel'], root: true},
            http: {verb: 'get', path: '/:id/get-relation-name'} //path '/:id/relation-name' already exists
          }
  );

[mplaza]

+1

[TeknoloGenie]

@jannyHou is this fix still being worked on? I can see that the limit works on filtering the relations but the sort still doesn't.

[dh376]

+1

[jannyHou]

@TeknoloGenie @ebarault @rpalacio86 @mplaza @dh376
I still plan to fix this, hasn't implemented it yet since there is a workaround for this and I am busy on something else, sorry for that.

For example:
A relation space hasManyThrough customer
Query by find and apply include filter would work:

// Work
// Result is ordered by `name`
Space.find({
      where: {id: space_id},
      include: {relation: 'customers', scope: {order: 'name'}}}, function(err, space){
      if (err) callback(err, null);
      callback(null, space);
 });

The signature doesn't work is query by findById and directly apply filter order on related model:

// Not work
     Space.findById(1, function(err, space){
       space.customers({
         order: 'name'
       }, function(err, result){
         if(err) callback(err, null);
         callback(null, result);
       });
     });

For a short term fix, could you use the first version instead? Fix the second one is not a simple job but I will work on it in the future for sure. Thanks for understanding:)

[PaddyMann]

+1 I have the same issue.

I have two models with a hasManyThrough relation: Country and Partner. I want to order the partners by the averageRating column in that model.

This works: /api/v2/partners?filter[order]=averageRating%20DESC
This does not: /api/v2/countries/GB/partners?filter[order]=averageRating%20DESC

Due to ER_BAD_FIELD_ERROR: Unknown column 'averageRating' in 'order clause'. SQL executed is "SELECT id,countryId,partnerId FROM CountryPartner WHERE countryId='GB' ORDER BY averageRating DESC"

I understand that there's a workaround if you jump into the code. But I'm expecting to use URLs like this in numerous places. Adding a new remote method in every location this is needed feels a bit crappy.

@jannyHou If I wanted to try and fix the underlying issue in loopback-datasource-juggler, where would I start? (it's not about to become my top priority, but would be useful to know if and when it does)

[jannyHou]

@PaddyMann thank you so much for trying to fix it, loopback-datasource-juggler contains complex logic in loopback, it's very easy to break other things even with a small change, I really appreciate your help but I'd better fix it myself so if I have any questions I can discuss with senior lb developers in time. Since many people are blocked by it, I label it as major here, will start to fix it next sprint.

[TeknoloGenie]

+1 @jannyHou thanks for the hard work! I've managed to find a workaround on the front-end to handle what i was needing for the time being, but it is moderately heavy, and won't work with large queries. So again, thanks for labeling this major!

[felipetoffolo1]

+1

[jreidal]

+1

[EvanWillms]

+1

[AndersonZacharyT]

+1

[ShaharHD]

+1

[thanickel]

+1

[thanickel]

@jannyHou If you can guide me where to look in the loopback-datasource-juggler code, I will fix this.

[RackyRose]

+1

[RLTmultimedia]

+1

[hoangtrongphuc]

Any update on this issues ?

[jannyHou]

@hoangtrongphuc I am working on a pr fixing it: https://github.com/strongloop/loopback-datasource-juggler/pull/1153

[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 https://github.com/strongloop/loopback-datasource-juggler/issues/166#issuecomment-295861916

[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 https://github.com/strongloop/loopback/issues/3487 reported.
And there is a PR fix opened for it https://github.com/strongloop/loopback-datasource-juggler/pull/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 https://github.com/strongloop/loopback/issues/3487, which should reflect the filter issue you run into recently.