Include related models with a custom scope

[bajtos]

Besides specifying the relation name to include, it's also possible to specify additional scope constraints:

• custom order, limit, skip and fields
• additional scope.where constraint

For example, the following filter will include only the first active product:

filter.include = [{
  relation: 'products',
  scope: {
    where: {active: true},
    limit: 1
  }
}

The initial version of Inclusion does not support custom scopes. In this story, we should improve findByForeignKeys function to support additional scope constraints.

LB3 test suite: loopback-datasource-juggler/test/include.test.js#L247-L253)

See also #3387

Acceptance criteria

• support nested include relations.
• add new mocha test cases to exercise these scenarios
• update docs (add nexted query examples)

[luncht1me]

Was trying to do a filter query

include: [
{ relation: 'relatedModel', scope: { include:[{ relation: 'relatedRelatedModel' }] } },
]

but got shut down by 'scope not supported' error. I see it's written in the type definitions though, is this just a bad package version on my part perhaps? Or is scope within a relation actually not hashed out completely yet?

How should we be handling nested relationships? ie: I have a source base model w/ a hasmany to a mapping model, which has a hasmany mapping to another target model. Should I just custom spin a controller method to do it all manually for now? It would be great to just use the inclusion resolver like this.

edit ---- right I see in the source it's not implemented, my bad. I'll spin up a custom method to link it all together for now.

[ChrisKretschmer]

I have the same issue and need to query nested relations...
Is there no way atm to do this automatically? I have a tree structure and don't want to query for each node seperatly :(

[st119848]

i need inclusion in inclusion toooo. @luncht1me

[theophane-girard]

Me too ! :)

[tipsypastels]

Would really like to see this added.

[ericalves]

Is too hard use lb4 without nested include relations.. mostly if you came from lb3..

[ericalves]

Was trying to do a filter query

include: [
{ relation: 'relatedModel', scope: { include:[{ relation: 'relatedRelatedModel' }] } },
]

but got shut down by 'scope not supported' error. I see it's written in the type definitions though, is this just a bad package version on my part perhaps? Or is scope within a relation actually not hashed out completely yet?

How should we be handling nested relationships? ie: I have a source base model w/ a hasmany to a mapping model, which has a hasmany mapping to another target model. Should I just custom spin a controller method to do it all manually for now? It would be great to just use the inclusion resolver like this.

edit ---- right I see in the source it's not implemented, my bad. I'll spin up a custom method to link it all together for now.

Do you can show to us what you did to get around this problem?

[dhmlau]

@agnes512 @bajtos, could you please add the acceptance criteria so that the team can estimate? Thanks!

[agnes512]

@bajtos do we want to support all query clauses include, where, etc. or filter.include only in this task?

[mamiller93]

Including the where in this scope would greatly help us out. While we don't need it for our current implementation (admin users can see everything), we will need to start filtering our related model results in the coming few months and having the ability to scope/where would be huge. It would be a shame to have to drop to a direct SQL implementation.

[jannyHou]

@agnes512 I believe this story only supports filter.include, the scope object is for the related items.

[samarpanB]

+1 We also need the same. When can we expect this ? I see its in December milestone. Is it correct ?

Including the where in this scope would greatly help us out. While we don't need it for our current implementation (admin users can see everything), we will need to start filtering our related model results in the coming few months and having the ability to scope/where would be huge. It would be a shame to have to drop to a direct SQL implementation.

[dhmlau]

@samarpanB @mamiller93, we acknowledged the importance of having a custom scope. Thanks for your input!
@samarpanB, yes, the plan is if we could finish Reject create/update requests when data contains navigational properties in Nov which has PR under review, we (possibly @agnes512) can start working this one. The challenge is that the team will start going away for holidays at different point in Dec but will try our best.

[bajtos]

do we want to support all query clauses include, where, etc. or filter.include only in this task?

I see two sub-features here:

(1) Filter which included models are returned, see the original issue description:

filter.include = [{
  relation: 'products',
  scope: {
    where: {active: true},
    limit: 1
  }
}]

(2) Recursive include (include models related to related models), as described in #3453 (comment):

filter.include = [{ 
  relation: 'relatedModel', 
  scope: {
    include: [{ 
      relation: 'relatedRelatedModel'
    }]
  },
}]

Ideally, we should support both flavors and also a combination of them. I think this should be actually easy to achieve, all we need to do is pass filter.include.scope to the relation resolver and the filter argument of find method called on the target repository. Fingers crossed 🤞

If my gut feeling is wrong, then we can pick first the option that is easier to implement and then decide what to do about the rest.

[agnes512]

closing as done

[pratikjaiswal15]

Hello. Can you please tell us the syntax for nested relations for url. I have tried something like this but it is giving error 500.

http://[::1]:3000/users?filter[include][0][relation]=carts&filter[include][0][scope]filter[include][0][relation]=product

I have three models. Users have has-many carts and carts belongs-to product.
My loopback/cli version is 1.27.0.
Thank you in advance

[dougal83]

Try incrementing the include filter as follows:

http://[::1]:3000/users?filter[include][0][relation]=carts&filter[include][1][scope][0]filter[where][product_id]=2

[pratikjaiswal15]

Still not working.

[dougal83]

Still not working.

In that case you'll probably have to provide an example repo reproducing the problem so someone can take a look for you.

[pratikjaiswal15]

I have posted a question on stack overflow https://stackoverflow.com/questions/59435371/loopback-4-include-nested-relations
Whereas multiple relations working well. I would like to know the syntax for nested relation[rest-api] as it is not documented

[shadyanwar]

@bajtos @agnes512 thank you for the great work.
While removing fields works well, specifying which fields to only show results in nothing coming back in response from the target model at all (comes as undefined). Your help is appreciated.

Examples:
This one works well. Returns all properties except the username:

await exampleModelRepository.find({ include: [{ relation: "user", scope: {fields: { username: false }  } }] } )

This one doesn't work. Returns the included model's parent property as undefined. Obviously, the intended behavior is to only show the username:

await exampleModelRepository.find({ include: [{ relation: "user", scope: {fields: { username: true }  } }] } )

[agnes512]

@pratikjaiswal15 hi, could you try to encode your filter with

encodeURIComponent(JSON.stringify({
  include: [
    {
      relation: 'carts',
      scope: {
        include: [{relation: 'product'}],
      },
    },
  ],
})
);

then do
http://[::1]:3000/users?filter= + the encode result ?

Sorry that we haven't found a proper way to query the nested relations with square brackets url format.
And since the query is getting complicated, we also recommend to just encode the filter with the above function.

[agnes512]

@shadyanwar I just tried it out and reproduced it.
I investigated a bit, it is interesting.. apparently the source key/foreign key needs to be included in the fields to make the inclusion work.

For example, for hasMany relation, Customer has many Orders, the foreign key Order.customerId needs to be included in the scope.fields

await customerRepo.find({
        include: [
          {relation: 'orders', scope: {fields: {name: true, customerId: true}}},
        ],
      });

Similarly, for belongsTo relation, an Order belongs to a Customer, the source id Customer.id needs to be included:

await orderRepo.find({
        include: [
          {relation: 'customer', scope: {fields: {name: true, id: true}}},
        ],
      });

I think our fields clause doesn't work well with such an inclusion use case, any thoughts? @strongloop/loopback-maintainers

[pratikjaiswal15]

@agnes512 No problem. Thank you.

@pratikjaiswal15 hi, could you try to encode your filter with

encodeURIComponent(JSON.stringify({
  include: [
    {
      relation: 'carts',
      scope: {
        include: [{relation: 'product'}],
      },
    },
  ],
})
);

then do
http://[::1]:3000/users?filter= + the encode result ?

Sorry that we haven't found a proper way to query the nested relations with square brackets url format.
And since the query is getting complicated, we also recommend to just encode the filter with the above function.

No problem. Thank you

[fabripeco]

Hi,

I'm wondering if the 'limit' param works if added to the scope of nested relations, to limit the number of items of a hasMany relation. I have a strange behaviour in the response.
I have a BusinessPartner model with hasMany relation to Addresses

@hasMany(() => BuspartnerAddress, {keyTo: 'clientId', name: 'addresses'})
addresses: BuspartnerAddress[];

I want to get the businessPartners with only the first address for each one. My filter param looks like

{
  "order": ["name ASC"],
  "where": {
    "bustype": "CUSTOMER"
  },
  "include": [
    {
      "relation": "addresses",
      "scope": {
        "skip": 0,
        "limit": 1,
        "where": {
          "deleted": false
        }
      }
    }
  ]
}

The where clause in the scope of the include works well, but the limit: 1 param returns the address for only one businessPartner, not one for each businessPartner. If I increase the value of the limit, e.g. 10, the response returns max 10 address, differently distribuited on the businessPartners.
It looks like the query on the related models was performed only once and not for each 'parent' instance (businessPartner).

Am I wrong in something? Is this the intended behaviour?

I'm using a postgresql connector and this is my ecosystem (Nodejs v12.16.2 npm v6.14.4 - Postresql 12)

├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── [email protected]
├── [email protected]

Thank you very much