Loopback-next: Inclusion of related models [MVP]

Created on 24 May 2018  Â·  50Comments  Â·  Source: strongloop/loopback-next

Description / Steps to reproduce / Feature proposal

Follow up task for PR #1342

Inclusion of related models (same as LB3)
For example, Customer model has {include: ['order']}. When query on Customer, the result should includes the Order array.

Duplicates:

Follow-up stories:

  • Add support for "include" and "fields" to findById (REST API) #1721

Acceptance Criteria

MVP scope

2019Q3

  • [x] Add keyFrom to resolved relation metadata #3441
  • [x] Test relations against databases #3442
  • [x] Add findByForeignKeys helper (initial version) #3443
  • [x] Introduce InclusionResolver concept #3445
  • [x] Include related models in DefaultCrudRepository #3446
  • [x] Implement InclusionResolver for hasMany relation #3447
  • [x] Implement InclusionResolver for belongsTo relation #3448
  • [x] Implement InclusionResolver for hasOne relation #3449
  • [x] Update todo-list example to use inclusion resolver #3450

2019Q4

  • [x] Reject create/update requests when data contains navigational properties #3439
  • [x] Add inclusion resolvers to lb4 relation CLI #3451
  • [x] Verify relation type in resolve{Relation}Metadata #3440
  • [x] Run repository tests for PostgreSQL #3436
  • [x] Run repository tests for Cloudant #3437
  • [x] Blog post: announce Inclusion of related models #3452

Out of MVP scope

  • [ ] Support inq splitting in findByForeignKeys #3444
  • [x] Include related models with a custom scope #3453
  • [ ] Recursive inclusion of related models #3454
  • [ ] Reject queries with incompatible "filter.fields" and "filter.include" #3455
  • [ ] Spike: robust handling of ObjectID type for MongoDB #3456

See https://github.com/strongloop/loopback-next/issues/3585 for the full list.

2019Q4 Juggler Relations epic feature parity p1 user adoption

Most helpful comment

Hello @bajtos , what's the update on this ? When can we expect this feature in LB4. We are using LB4 since its GA, but this missing feature is making us to not to move to production. Majorly due to performance issues. In order to fetch related models, we have to query other repositories manually to fetch data which in case of find method is too many DB hits.
We really need this.

All 50 comments

I added the following acceptance criteria:

  • [ ] Modify the implementation of the following DefaultCrudRepository methods to correctly support inclusion of related models as configured via filter.include property.

    • [ ] find

    • [ ] findOne

    • [ ] findById

  • [ ] Include integration-level tests using in-memory database to verify the implementation.
  • [ ] Add new acceptance-level tests for "find" method to verify how to send a REST request to find models including related models. Because the REST API is generated by our CLI, I think these tests should be implemented in the todo-list example repository.

I think we need a spike to determine how to actually implement relation traversal, because right now, a repository for source model does not necessarily know how to obtain an instance of a repository for accessing the target model.

/cc @dhmlau

@bajtos , I've created the spike https://github.com/strongloop/loopback-next/issues/1952. I've copied and pasted part of the acceptance criteria over there. Could you please review? Thanks.

Cross-posting from #1939.

I realized this PR is just a starting point to fully support include. There are a few issues:

@dhmlau The inclusion of related models should work with belongsTo relation like LB3.

I was thinking about this problem and perhaps we can implement this at LB4 Repository level?

Let's say "Customer" has many "Order" instances, the relation is called "orders" and we want to fetch a customer with their orders.

The way how inclusion works in juggler and LB 3.x, such request creates multiple queries:

  1. First, Customer instances are fetched the usual way.
  2. Secondly, we process "filter.include" entries and load related models - see lib/include.js. The relation name (e.g. "orders") is provided by the caller in filter.include and juggler uses the relation name to look up the relation definition, the target model, etc.

For LoopBack 4, I am thinking about making the relation lookup more explicit and letting the Repository to specify a lookup table for inclusion. Similarly to how we explicitly build HasManyRepositoryFactory and BelongsToAccessor now.

A mock-up usage to illustrate what I mean:

export class TodoListRepository extends DefaultCrudRepository<
  TodoList,
  typeof TodoList.prototype.id
> {
  public readonly todos: HasManyRepositoryFactory<
    Todo,
    typeof TodoList.prototype.id
  >;

  constructor(
    @inject('datasources.db') dataSource: juggler.DataSource,
    @repository.getter(TodoRepository)
    protected todoRepositoryGetter: Getter<TodoRepository>,
  ) {
    super(TodoList, dataSource);
    this.todos = this._createHasManyRepositoryFactoryFor(
      'todos',
      todoRepositoryGetter,
    );
   ////// THE FOLLOWING LINE IS NEWLY ADDED
   this._registerHasManyInclusion('todos', todoRepositoryGetter);
  }
}

export class TodoRepository extends DefaultCrudRepository<
  Todo,
  typeof Todo.prototype.id
> {
  public readonly todoList: BelongsToAccessor<
    TodoList,
    typeof Todo.prototype.id
  >;

  constructor(
    @inject('datasources.db') dataSource: juggler.DataSource,
    @repository.getter('TodoListRepository')
    protected todoListRepositoryGetter: Getter<TodoListRepository>,
  ) {
    super(Todo, dataSource);

    this.todoList = this._createBelongsToAccessorFor(
      'todoList',
      todoListRepositoryGetter,
    );
   ////// THE FOLLOWING LINE IS NEWLY ADDED
    this._registerBelongsToInclusion('todoList', todoListRepositoryGetter);
  }
}

Implementation-wise, the base Repository implementation should process include inside the find method. A mock-up:

class DefaultCrudRepository {
  // ...
  _inclusions: {[key: string]: InclusionHandler};

  async find(filter?: Filter<T>, options?: Options): Promise<T[]> {
    const include = filter.include;
    filter = Object.assign({}, filter, {include: undefined});

    const models = await ensurePromise(
      this.modelClass.find(filter as legacy.Filter, options),
    );
    const entities = this.toEntities(models);

    for (relationName of include) {
      const handler = this._inclusions[relationName];
      if (!(handler)) throw new HttpErrors.BadRequest('Invalid inclusion.');
      await handler.fetchIncludedModels(entities, key);
    }
  }

  _registerHasManyInclusion(relationName, targetRepoGetter) {
    const meta = this.entityClass.definition.relations[relationName];
    this._inclusions[relationName] = new HasManyInclusionHandler(meta, targetRepoGetter);
  }
}

class HasManyInclusionHandler {
  constructor(public relation, public repositoryGetter) {}

  fetchIncludedModels(entities, relationName) {
    // see https://github.com/strongloop/loopback-datasource-juggler/blob/f0a6bd146b7ef2f987fd974ffdb5906cf6a584db/lib/include.js#L609-L634
   const objIdMap2 = includeUtils.buildOneToOneIdentityMapWithOrigKeys(entities, this.relation.keyFrom);

    const filter = {};
    filter.where[relation.keyTo] = {
      inq: uniq(objIdMap2.getKeys()),
    };

    const targets = findWithForeignKeysByPage(
      await this.repositoryGetter(), filter, this.relation.keyTo, 0);
   const targetsIdMap = includeUtils.buildOneToManyIdentityMapWithOrigKeys(
      targets, this.relation.keyTo);
    // store targets to entities, e.g. 
    // set entities[0].orders to the target with "customerId=entities[0].id"
  }

Hello @bajtos , what's the update on this ? When can we expect this feature in LB4. We are using LB4 since its GA, but this missing feature is making us to not to move to production. Majorly due to performance issues. In order to fetch related models, we have to query other repositories manually to fetch data which in case of find method is too many DB hits.
We really need this.

Is this issue still there? I'm trying to execute a code similar to the todo-list tutorial and it is not working... using MySQL...

Thinking in how the relation works, why instead of create a relation the feature dos not include the model itself?
What does it means in my mind?
Be able to do something like:
/customer/{id}/orders/count
or
/customer/{id}/orders/{id}/items (If Item is created as an collection inside orders).

Looks OK? I'm missing something?

Please Help! I need a solution to save my Day !

i need this feature, there is any other solution

Also i need this feature,
But how i can use QueryBuilder Juggler and get left join table,
I would create new function in Repository and use it in controller,
This solution it is recommended ?
Any exemple ?

Explore the way to resolve inclusion property is listed in February 2019 milestones. So, hopefully we will have something by the beginning of next month.

_Cross-posting the list of stories created in [Spike] Explore the way to resolve inclusion property #2152:_

  • Fix repository-json-schema to handle circular references #2628
  • Allow controllers to provide definitions of models referenced in operation spec #2629
  • Enhance getJsonSchema to describe navigational properties #2630
  • Implement getJsonSchemaRef and getModelSchemaRef helpers #2631
  • Modify Repository find* methods to include navigational properties #2632
  • Add navigational properties to examples/todo-list #2633
  • Spike: Resolver for inclusion of related models #2634

Hello @bajtos , When can we expect this feature to land ? Its a must-have for a production app. If you need help to make this feature to complete sooner, I can gladly contribute with your guidance. Please let me know.

@samarpanB, thanks for your offer! We also wish to get it completed soon.
Currently there are 2 tasks that are part of this epic. We are planning them for June milestone, but if you could contribute, that would be perfect.

@dhmlau Let me try the second one (#2630 ) first.

Thanks @samarpanB. I just assign you that issue. Thanks!

I have updated the issue description with a list of tasks created from the spike #3387.

When we should expect this issue to be resolved? Or if you have a workaround to do this let me know. Thanks :)

We're planning to get the MVP done in the Q3 "release" (we do continuous delivery).

@bajtos, IIRC you've shared some workaround somewhere (in Slack). Is it applicable for everyone or particular use case? Thanks.

Gah, I thought @loopback/repository 1.13.0 would include the inclusion resolvers. Do you have a rough estimate of when 2019Q4 ships, now that this has a Q4 tag? Or is it still on track for Q3?

@dhmlau thanks for keeping us updated 👍

I would like to contribute to this. I have not contributed before.
What could be a good start?
What do you think I could help on with?
@bajtos

@mamiller93, we wish to complete as much as possible for the MVP of this story. For the Sept milestone, our goal is to finish the following tasks (and we're getting close!):

  • Implement InclusionResolver for hasMany relation #3447
  • Implement InclusionResolver for belongsTo relation #3448
  • Implement InclusionResolver for hasOne relation #3449
  • Add keyFrom to resolved relation metadata, #3441

From my understanding, after all that, we will be able to update the todo list tutorial to use the inclusion resolvers via https://github.com/strongloop/loopback-next/issues/3450.

There are some more post-MVP enhancements, see https://github.com/strongloop/loopback-next/issues/3585.

@collaorodrigo7, I'll let @bajtos and @agnes512 to comment on how you can help. Thank you first!

Thanks @dhmlau, I will be waiting!

Hi,

Do you know how to do a where filter on data retrieved by the inclusion resolver ?
For exemple : Author(id, name) Book(id, title, extract, category, authorId)
/authors?filter[include][0][relation]=books&filter[where][books][category]=SF
/authors?filter[include][0][relation]=books&filter[where][books][][category]=SF
/authors?filter[include][0][relation]=books&filter[where][books.authorId][category]=SF
/authors?filter[include][0][relation]=books&filter[where][books.authorId][][category]=SF
No one of them works
Thanks

@sestienne Hi!

Do you know how to do a where filter on data retrieved by the inclusion resolver ?

Here is an example for including a todoList instance of a todo that has id = 1 via belongsTo relation.

http://127.0.0.1:3000/todos/1?filter[include][][relation]=todoList

does inclusion resolver working in acceptance tests?

Yes! We've tested it over SQL and NoSQL databases. You can check the Querying related models section in each relation for the usages on our site. You can also check out the acceptance test if you're interested.

@agnes512 Thanks a lot for your response.
I finally success to test inclusion resolver in acceptance tests.
By the way, I success with something like /authors?filter[include][][relation]=books or /authors/1?filter[include][][relation]=books, but whith a where filter it doesn't work.
I will try to test with todolist example with this request :
http://127.0.0.1:3000/todos?filter[include][][relation]=todoList&filter[where][todos][][isComplete]=false

@agnes512 So, I launch the todolist example and try request like :
http://127.0.0.1:3000/todo-lists?filter[include][][relation]=todos&filter[where][todos][][desc]=MWAHAHAHAHAHAHAHAHAHAHAHAHAMWAHAHAHAHAHAHAHAHAHAHAHAHA
But it doesn't work. What's wrong ?

@sestienne sorry I didn't realize that you were trying to filter out todos.

In url GET /todo-lists?filter[where][id]=1&filter[include].., the where filter only filters out todo-list. It's the where filter inside of scope of include filters out todos.

{
  "where": {// filters todo-list},
  "include": [
    {
      "relation": "string",
      "scope": {
        "where": { // filters todo },
       ...
      }
    }
  ]
}

Unfortunately we don't support inclusion with custom scope at the moment.

OK thanks, do you know any planned date for this feature ?

The task #3453 is out of the scope of this MVP. So it probably won't be up that soon.

https://stackoverflow.com/q/58742689/7628381
Can anyone please tell how to add inclusion resolvers

@pratikjaiswal15 Hi, I believe that the code you showed in the SO has already setup the inclusion resolver for the relation retailers ( I assume the rest of code are correct).
You should be able to use it _at the repository level_ . For example, if you want to find a Retailer that has id=1, you can do

let result = await someClassRepository.findById(1, {include: [{relation: 'retailers'}]});

If you'd like to do CRUD operations at the repository level, here are some examples of usage of inclusion resolver in our test case.

If you'd like to use it with REST endpoints, set up the controller as usual, and the url

 GET http://localhost:3000/someClasses/1?filter[include][][relation]=retailers

should give you the same result as what it gets at the repository level. Reference: inclusion resolver blog.

I've update the corresponding docs in https://github.com/strongloop/loopback-next/pull/4007. But it seems like the site hasn't updated yet.

Let me know if you need more help, thanks.

Thank you for the response. I have just updated loopback-cli version from 1.21.4 to 1.24.0. So for new project inclusion resolver is working fine but for the old project which built with 1.21.4 version is giving error following line

this.registerInclusionResolver('retailers', this.retailers.inclusionResolver);
and also for every command with such as lb4 model etc it is givivng error as folllows

`The project was originally generated by @loopback/[email protected].
The following dependencies are incompatible with @loopback/[email protected]:
- typescript: ~3.5.3 (cli ~3.6.4)
? Continue to run the command? (y/N)`

There are some issues with typescript. How to resolve it . thank you

@pratikjaiswal15 the error is because he older project is generated by 1.21.4 and so is the app dependencies.

If you want to use the latest cli, you need to upgrade dependencies to match the newer CLI ( you can force it or upgrade deps to versions matching cli 1.24.0. Command lb4 -v shows a list of compatible versions).
Ref: Upgrading LoopBack Dependencies

After deps upgrade, you might have to fix the project manually for any errors.

Thank you for your reply. Inclusion resolver with has many relations is working pretty well. But with belongs to the relation there are some conservations. In my example, the retailer belongs to users.

http://[::1]:3000/retailers?filter[include][][relation]=users&filter[where][retailer_id]=1
This extension is working and including both tables users and retailers

http://[::1]:3000/retailers/1?filter[include][][relation]=users&filter[where][retailer_id]=1

`But this extension is not working. It is simply giving retailer data with id = 1 and not any user data.

And another question is how to include three or more models because
http://[::1]:3000/users?filter[include][][relation]=retailers&filter[include][][relation]=hotels

This extension is giving internal server error.
Thank you in advance

Sir, does loopback's current version supports inclusion of three models where a model has has many relation with other two? If not then what is alternative.

@pratikjaiswal15
For question 1, you need to replace the endpoint @get('/todo-lists/{id}', in your Retailer controller to:

  @get('/retailers/{id}', {
    ...
    ...
  async findById(
    @param.path.number('id') id: number,
    @param.query.object('filter', getFilterSchemaFor(Retailer))
    filter?: Filter<Retailer>,
  ): Promise<Retailer> {
    return this.todoListRepository.findById(id, filter);
  }

As for question 2, yes it is possible to include multiple relations.
with controllers, for your example, use the url: ( the include here is an array)

http://[::1]:3000/users?filter[include][0][relation]=retailers&filter[include][1][relation]= hotels

with repositories, you can do:

const result = await userRepo.find({
        include: [{relation: 'retails'}, {relation: 'hotels'}],
      });

Notice that you need to make sure all relation names are unique.

And thanks for your comments. They are good suggestions of improving our docs!

Thank you very much. Both solutions working.

@raymondfeng agnes512

hello sir,can you help me, i new in loopback 4 and i stiil get stuck, how to include three or more models :
Page,Sections,Content
and this is my code for pageController

`@get('/pages/{id}', { 
  async findById(
    @param.path.number('id') id: number,
    @param.query.object('filter', getFilterSchemaFor(Page))filter?: Filter<Page>,
  ): Promise<any> {
      return this.pageRepository.findById(
         id, 
        { include: [{relation: 'sections'}, {relation: 'content'}]}
    );
  }
}

i just have relation for sections
i want to include section and content too

thanks `

@muhrifai7 have you tried the solution that I post above? i.e, modify your GET /pages/{id} endpoint to

  @get('/pages/{id}', {
    responses: {
      '200': {
        description: 'Page model instance',
        content: {
          'application/json': {
            schema: getModelSchemaRef(Page, { includeRelations: true }),
          },
        },
      },
    },
  })
  async findById(
    @param.path.number('id') id: number,
    @param.query.object('filter', getFilterSchemaFor(Page)) filter?: Filter<Page>
  ): Promise<Page> {
    return this.pageRepository.findById(id, filter);
  }

Usually we don't set the inclusion in the endpoint. We hand it to the filter to take care to make the endpoint flexible.

For example, with the code above, you can include sections relation only with http request:

http://[::1]:3000/pages?filter[include][][relation]=sections

You can also include multiple relations with request:

http://[::1]:3000/pages?filter[include][0][relation]=sections&filter[include][1][relation]= content

@muhrifai7 have you tried the solution that I post above? i.e, modify your GET /pages/{id} endpoint to

  @get('/pages/{id}', {
    responses: {
      '200': {
        description: 'Page model instance',
        content: {
          'application/json': {
            schema: getModelSchemaRef(Page, { includeRelations: true }),
          },
        },
      },
    },
  })
  async findById(
    @param.path.number('id') id: number,
    @param.query.object('filter', getFilterSchemaFor(Page)) filter?: Filter<Page>
  ): Promise<Page> {
    return this.pageRepository.findById(id, filter);
  }

Usually we don't set the inclusion in the endpoint. We hand it to the filter to take care to make the endpoint flexible.

For example, with the code above, you can include sections relation only with http request:

http://[::1]:3000/pages?filter[include][][relation]=sections

You can also include multiple relations with request:

http://[::1]:3000/pages?filter[include][0][relation]=sections&filter[include][1][relation]= content

thanks for your response sir,

it still doesnt works, because the relation is Page hasMany Section hasMany Content, so i want to retrive the data like this :

[
   {
      "id": 1,
     "name": "LandingPage",
     "sections": [
          {
              "id": 2,
             "pageId": 1,
             "name": "string",
             "header": "string",
             "sub_header": "string",
              “contents” : [
                 {
                    “id”:1,
                  “sectionId”: 2
                }
        ]
       }
    ]
  }
]

not like this

{
       "id": 1,
       "name": "LandingPage",
       "sections": [
          {
             "id": 2,
             "pageId": 1,
             "name": "string",
             "header": "string",
             "sub_header": "string",
           }
      ],
      “contents” : [
   {
           “id”:1,
         “sectionId”: 2
     }
  ]
}

@muhrifai7 I see. So you would like to traverse nested relations not multiple relations. Currently we don't support this feature yet. See https://github.com/strongloop/loopback-next/issues/3453. Feel free to join the discussion there :D

Hi,

Is it possible to replace the optional attribute with a required attribute for relationships included in openApi? And the Id, he returned by the Api

@hasMany(() => Role)
roles: Role[];
@get('/users', {
    responses: {
      '200': {
        description: 'Array of Users model instances',
        content: {
          'application/json': {
            schema: {
              type: 'array',
              items: getModelSchemaRef(User, {includeRelations: true}),
            },
          },
        },
      },
    },
  })

OpenApi.json

"UserWithRelations": {
  "title": "UserWithRelations",
  "description": "(Schema options: { includeRelations: true })",
  "properties": {
    "id": {
      "type": "number"
     },
     "name": {
       "type": "string"
      },
      "roles": {
         "type": "array",
         "items": {
            "$ref": "#/components/schemas/RoleWithRelations"
          }
       }
   },
  "required": [
     "name",
   ],
  "additionalProperties": false
},

To avoid in my client to make conditions everywhere to know if "roles" is not undefined, while in any case an empty array will be returned if there is no relation.

OpenApi :

/**
  * 
  * @type {Array<RoleWithRelations>}
  * @memberof UserWithRelations
*/
roles?: Array<RoleWithRelations>;

thanks

@KyDenZ If I understand correctly, your expected schema with related items is

"UserWithRelations": {
  "title": "UserWithRelations",
  "description": "(Schema options: { includeRelations: true })",
  "properties": {
    "id": {
      "type": "number"
     },
     "name": {
       "type": "string"
      },
      "roles": {
         "type": "array",
         "items": {
            "$ref": "#/components/schemas/RoleWithRelations"
          }
       }
   },
  // DIFFERENCE
  // `roles` should be added to `required`
  "required": [
     "name", "roles"
   ],
  "additionalProperties": false
},

?

That's exactly, with the Id also in required, but that it is generated automatically

Is it possible to replace the optional attribute with a required attribute for relationships included in openApi?

At the moment, the schema describes the responses that can be returned by the server. If we modify the schema and make a navigational property like roles required, then we also have to ensure that we always fetch that relation when querying models. Otherwise there will be a mismatch between the schema and the actual response body.

Please note that by default, LoopBack does not include any related models. Assuming User has-many Role instance: if you call GET /users, we return only user data, no roles. If UserWithRelations sets roles as a required property, then some part of the app must ensure that GET /users is eventually converted to database query (LoopBack filter) {include:[{relation: 'roles'}]}.

How do you envision to implement this part?


Using the current features provided by LoopBack, my recommendation is to use function composition to apply any tweaks to the model schema produced by LoopBack.

For example, you can write a function like this:

function withRequiredProps(propNames: string[], schemaRef: SchemaRef) {
  const result = _.deepClone(schemaRef);
  const title = schemaRef.$ref.match(/[^/]+$/)[0];
  const modelSchema = result.components.schemas[title];
  if (!modelSchema.required)
    modelSchema.required = [];
  modelSchema.required.push(...propNames);
  return result;
}

Then you can use it as follows:

@get('/users', {
    responses: {
      '200': {
        description: 'Array of Users model instances',
        content: {
          'application/json': {
            schema: {
              type: 'array',
              // LOOK HERE
              items: withRequiredProps(
                ['roles'],
                getModelSchemaRef(User, {includeRelations: true}),
              ),
            },
          },
        },
      },
    },
  })

@bajtos
Great, thank you !

If it interests someone I just had to change some elements for it to work:

import { SchemaRef } from '@loopback/rest';
import * as _ from 'lodash';

export function withRequiredProps(propNames: string[], schemaRef: SchemaRef) {
  const result = _.cloneDeep(schemaRef); // Replace deepClone by cloneDeep
  const title = schemaRef.$ref.match(/[^/]+$/)![0]; // Add !
  const modelSchema = result.definitions[title]; // Replace components.schemas by definitions
  if (!modelSchema.required) modelSchema.required = [];
  modelSchema.required.push(...propNames);
  return result;
}


It would be interesting to propose as in loopback 3 a "scope" attribute which allows to automatically include relations. This makes it possible to generate a schema with the required relationships for all requests.

For a specific route, I like your solution. This function should be added in @ loopback/rest and later supported nested includes, when loopback will support this feature.

This epic is done! Thanks @agnes512 @nabdelgadir @bajtos!
Closing this issue as done. If there's any outstanding discussion, please feel free to open a new issue. Thanks.

Was this page helpful?
0 / 5 - 0 ratings

Related issues

shahulhameedp picture shahulhameedp  Â·  3Comments

joeytwiddle picture joeytwiddle  Â·  3Comments

mhdawson picture mhdawson  Â·  3Comments

mightytyphoon picture mightytyphoon  Â·  3Comments

ThePinger picture ThePinger  Â·  3Comments