Include and filter by properties in nested relations

[kevinbioj]

Hi,

I'm trying to get relations to work with nested relations. Let's say I have entities A, B, and C. A has a Many-to-One to B, and B has a One-to-One to C. If I am using the pagination on A, but trying to filter by values on C, it looks like I can't. relations option seems to only accept "surface" relations, and I've been trying to build a custom query builder but even if I can manage to include these relations, filtering seems to still be broken.

Am I missing something? Thanks.

[emrivero]

Hi,

I'm trying to get relations to work with nested relations. Let's say I have entities A, B, and C. A has a Many-to-One to B, and B has a One-to-One to C. If I am using the pagination on A, but trying to filter by values on C, it looks like I can't. relations option seems to only accept "surface" relations, and I've been trying to build a custom query builder but even if I can manage to include these relations, filtering seems to still be broken.

Am I missing something? Thanks.

Hello,

same problem here. I was investigating the code and testing in my local machine and it seems that it doesn't do the LEFT-JOIN of third or deeper entities correctly.

I made a fork of the library and modified what I needed to have this functionality.

When you configure the pagination it would look something like this:

const PAGINATE_CONFIG: PaginateConfig<OfficeEntity> = {
    searchableColumns: [
      'column1',
      'column2',
      'entityA.name',
      'entityA.entityB.name',
    ],
    // ...options
    deepRelations: [
      {
        'entityB': 'entityC',
      },
    ],
  };

executePaginate(query: PaginateQuery) {
    return paginate(
      query,
      this.repository,
     PAGINATE_CONFIG,
    ); // => [{ column1, column2, ..., entityB: [{ colum1b, column2b, EntityC: {[ column1c, column2c, ...}] }] }]


// general case

const paginateOptions = {
   ..., // options 
   deepRelations: [
      {
        'entity1': {
          'entity2': {
            'entity3': {
             ....................
           .............,
            'entityN-1': 'entityN'
             }
          }
      },
    ],
}

// Also: A --> B --> C and A --> D -->E
const paginateOptions = {
   ..., // options 
   deepRelations: [{
        'entityB': 'entityC',
        'entityD': 'entityE'
}]

In principle it would work for N-depth relationships without any problem although it would be nice to implement a circular dependency detector in order to know when someone misuses the deepRelations parameter.

If @ppetzold likes this feature I would try to make PR ASAP.

If you @kevinbioj need it already, I could push it the version to my fork but you would use it as unofficial version (as I am doing now).

If you are interested in this I hope you answer Best regards

[kevinbioj]

Hey, I no longer work on the project that required this, I managed to find a workaround by redesigning the data scheme (which wasn't really perfect anyway). However I think a lot of people would benefit from this, so I'd say go ahead :ok_hand: and thank you for your contribution!

[MaximilianOtto]

Is there a pull request for this? I would love to use this feature! :)

[ppetzold]

Is there a pull request for this? I would love to use this feature! :)

Nope. You are welcome to give it a try :)

[MaximilianOtto]

It seems like @emrivero already implemented it. I would like to avoid double the effort ;)

[emrivero]

@FlyingOddo @ppetzold Hey! Apologies for forgetting to do the PR. As I had to make some more changes I have to check that the tests are passing I will do it as soon as possible.

[MaximilianOtto]

Hi @emrivero Thank you!! :) keep me informed if there is a pull request

[Djobby]

Hi @emrivero I just had this same problem 😕 So I'm very interested in this feature too! 😃

[thegriglat]

Hi all, it seems I found a workaround for nested relations

The main idea is to use custom queryBuilder which will mask needed relation as core entity's relation

Assume the following relations between entities: User -> Photos -> Comments and we need to filter by comments.likes property. When we will use comments.likes in filter or searchBy query -- nestjs-paginate will try to find user.comments property in user_comments table name -- and use our manual left join! PROFIT!

Snipper to illustrate the idea (at least smth like that works in my project):

const qb = this.userRepository
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.photos', 'user_photos')
      // fake user.photos.comments relation, so paginate will assume that comments is user's property
      .leftJoinAndSelect('user_photos.comments', 'user_comments')

    await paginate<User>(request, qb, {
      relations: ['photos'],
      filterableColumns: {
        // use comments.likes as paginate will append `user_` prefix and will use left join above
        'comments.likes': [FilterOperator.EQ, FilterOperator.NOT, FilterOperator.IN],
        // any needed here as comments.likes isn't user property and otherwise will break TS types
      } as any,
    })

[ppetzold]

Closed with #369

[cristianCeamatuAssist]

I am confused with what is the final solution to this issue. Is @thegriglat workaround the correct approach?

[ppetzold]

query builder can be used, or we support the following nested relations notation:

https://github.com/ppetzold/nestjs-paginate#usage-with-nested-relations

[cristianCeamatuAssist]

query builder can be used, or we support the following nested relations notation:

https://github.com/ppetzold/nestjs-paginate#usage-with-nested-relations

Fantastic! Thank you for taking care of that!