Loopback 4 filters not working with multiple relations

[pratikjaiswal15]

I am using multiple relations in loopback 4. I have 4 models.

User has many carts accessible on url users/user_id/carts
cart has many products. Relation named product
Product has many stocks and product-prices named stocks and productPrices respectively.
Here is a controller at url users/user_id/carts/prices which performs multiple relations as shown below.

get('/users/{id}/carts/prices', {
        responses: {
          '200': {
            description: 'Array of Cart\'s belonging to Users',
            content: {
              'application/json': {
                schema: { type: 'array', items: getModelSchemaRef(Cart) },
              },
            },
          },
        },
      })
      async Multiple_relation(
        @param.path.number('id') id: number,
        @param.query.object('filter') filter?: Filter<Cart>,
      ): Promise<Cart[]> {
        return this.usersRepository.carts(id).find({
          include: [{
            relation: 'product',
            scope: {
              include: [{ relation: 'productPrices' }, { relation: 'stocks' }],
            },

          },
          ],
        }, filter);
      }

But I am unable to perform a filter on this url like

users/29/carts/prices?filter[where][cart_id]=29

The above query doesn't filter records where cart_id is 29. What is going wrong?

Thank you in advance

[pratikjaiswal15]

Any update sir @agnes512

[agnes512]

@pratikjaiswal15 From your description, I guess you'd like to combine the where clause with the include clause. But notice that for the find() method, from the API docs, it takes 2 params:

find(
    filter?: Filter<T>,
    options?: Options,
  ): Promise<>{..}

That's why your filter filter[where][cart_id]=29 gets discarded, and

return this.usersRepository.carts(id).find({
          include: [{
            relation: 'product',
            scope: {
              include: [{ relation: 'productPrices' }, { relation: 'stocks' }],
            },

          },
          ],
        }, filter);

would only return all products with their related Stock and ProductPrice.

Basically, you need to combine these 2 filter. Here are two ways to do so::

1. use filterBuilder ( a formal, safer way):
   add: import filterBuilder from '@loopback/repository';
   modify your endpoint:

get('/users/{id}/carts/prices', {
...
      })
      async Multiple_relation(
        @param.path.number('id') id: number,
        @param.query.object('filter') filter?: Filter<Cart>,
      ): Promise<Cart[]> {
         // define the where clause here. You might want to check the existence of filter.where
         const where = filter!.where as Where<Cart>; 
        // if your filter might contain other clauses, you need to define and add them to the filterBuilder respectively 

         const filterBuilder = new FilterBuilder();
         const newFilter = filterBuilder
          .include(
             {relation: 'product',  scope: { include: [{ relation: 'productPrices' }, { relation: 'stocks' }]}}
          )
         .where(where);  // the where clause you defined above
         .build();

    return this.usersRepository.carts(id).find(newFilter);
}

2. Just combine these two filters if you make sure that you would only simply have the where clause in your query:

get('/users/{id}/carts/prices', {
...
      })
      async Multiple_relation(
        @param.path.number('id') id: number,
        @param.query.object('filter') filter?: Filter<Cart>,
      ): Promise<Cart[]> {
      // combine 2 filters
      const myFilter = {...filter, include: [{
        relation: 'product',
        scope: {
          include: [{ relation: 'productPrices' }, { relation: 'stocks' }],
        },
       }],
     };
  return this.usersRepository.carts(id).find(myFilter);
}

[pratikjaiswal15]

Okay, thank you

…

On Fri, 14 Feb 2020, 9:46 pm Agnes Lin, ***@***.***> wrote: @pratikjaiswal15 <https://github.com/pratikjaiswal15> From your description, I guess you'd like to combine the where clause with the include clause. But notice that for the find() method, from the API docs, it takes 2 params: find( filter?: Filter<T>, options?: Options, ): Promise<>{..} That's why your filter filter[where][cart_id]=29 gets discarded, and return this.usersRepository.carts(id).find({ include: [{ relation: 'product', scope: { include: [{ relation: 'productPrices' }, { relation: 'stocks' }], }, }, ], }, filter); would only return *all* products with their related Stock and ProductPrice . Basically, you need to combine these 2 filter. Here are two ways to do so:: 1. use filterBuilder *( a formal, safer way)*: add: import filterBuilder from ***@***.***/repository'; modify your endpoint: get('/users/{id}/carts/prices', { ... }) async Multiple_relation( @param.path.number('id') id: number, @param.query.object('filter') filter?: Filter<Cart>, ): Promise<Cart[]> { // define the where clause here. You might want to check the existence of filter.where const where = filter!.where as Where<Cart>; // if your filter might contain other clauses, you need to define and add them to the filterBuilder respectively const filterBuilder = new FilterBuilder(); const newFilter = filterBuilder .include( {relation: 'product', scope: { include: [{ relation: 'productPrices' }, { relation: 'stocks' }]}} ) .where(where); // the where clause you defined above .build(); return this.usersRepository.carts(id).find(newFilter); } 1. Just combine these two filters *if you make sure* that you would only simply have the where clause in your query: get('/users/{id}/carts/prices', { ... }) async Multiple_relation( @param.path.number('id') id: number, @param.query.object('filter') filter?: Filter<Cart>, ): Promise<Cart[]> { // combine 2 filters const myFilter = {...filter, include: [{ relation: 'product', scope: { include: [{ relation: 'productPrices' }, { relation: 'stocks' }], }, }], }; return this.usersRepository.carts(id).find(myFilter); } — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#4644>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHOOJOGHUXHOMKS7CVCEULTRC27XDANCNFSM4KUWSSOQ> .

[achrinza]

@agnes512 AFAIK filterBuilder (and similar functions) aren't documented. Should this go under Working with Data?

See #2527

[agnes512]

@achrinza Do you mean the page that we are having for LB4?

[achrinza]

@agnes512 yup

…

________________________________ From: Agnes Lin <[email protected]> Sent: Monday, February 17, 2020 11:13:20 PM To: strongloop/loopback-next <[email protected]> Cc: Rifa Achrinza <[email protected]>; Mention <[email protected]> Subject: Re: [strongloop/loopback-next] Loopback 4 filters not working with multiple relations (#4644) This email was sent from outside the organization. Please be cautious, the sender may not be who they claim to be. @achrinza<https://github.com/achrinza> Do you mean the page that we are having for LB4? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#4644>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AF73T6ZOCJLGRA5DSMZOAIDRDKSRBANCNFSM4KUWSSOQ>.

[agnes512]

That's a good suggestion! filterBuilder would be helpful to simplifies the query syntax for use cases like this.

[agnes512]

I am closing the issue. Feel free to reopen it if you have any questions, thanks!