Crud: nested joins: regression since v4.6.0?

[jbrousseau]

There seems that a regression was introduced since v4.6.0

Here is my controller:

@Crud(
[...]
query: {
        join: {
            posts: {}
            posts.comments: {}
            posts.comments.user: {}
       }
})
export class PostsController {

And HTTP GET:

http://localhost:3000/posts?join=posts&join=posts.comments&join=posts.comments.user

Error:

Error: Relation with property path comments.user in entity was not found.

It works with v4.5.0

After some debugging, the TypeORM relation path used for user entity is:
In v4.5.0:
comments.user
in v4.6.0:
posts.comments.user

Here is the line where the error is thrown:
function setJoins in packages/crud-typeorm/src/typeorm-crud.service.ts

builderrelationType;

I don't know if it can be fixed easily or not, though.

Thanks for your feedback!

[zMotivat0r]

adding aliases will allow to join

[jbrousseau]

Oh you're right, thanks for your feedback.
I've fixed it like that:

@Crud(
[...]
query: {
        join: {
            posts: {}
            posts.comments: { alias: 'postsComments'}
            posts.comments.user: { alias: 'postsCommentsUser'}
       }
})
export class PostsController {

With the HTTP GET that remains the same.
Thanks!

[jeffreyschultz]

Even with the alias I am still getting this error.

Update:

I had to add an alias to 'sections', 'sections.comments', and 'sections.comment.replies', and not just 'sections.comments' and 'sections.comment.replies'.

I am not sure, but this might be a bug in typeorm. I see that instead of breaking down the nested relation into multiple joins with aliases, the entire nested path is passed to Typeorm, and the cause of this issue.

This call to findRelationWithPropertyPath(this.relationPropertyPath) fails with this.relationPropertyPath being comments.replies. I would think that if you are searching within the parent alias, that it would be able to find comments.replies, unless it isn't capable of handling that level of nesting.

Further investigation, that call to findRelationWithPropertyPath(this.relationPropertyPath) is not capable of handling the nesting. In the screen shot, this.relations only contains propertyPaths like sections, or comments--nothing like comments.replies, so this call returns nothing.

Update:

It seems this is by design in Typeorm. It wants you to turn that nesting into multiple joins with multiple aliases, such as the snippet below.

const user = await this.repo
      .createQueryBuilder('user')
      .innerJoinAndSelect('user.accounts', 'accounts')
      .leftJoinAndSelect('accounts.expenses', 'expenses')
      .leftJoinAndSelect('expenses.projects', 'projects')
      .where('user.id=:user_id')
      .setParameter('user_id', id)
      .getOne()

Here are the changes I ended up making to @Crud, and maybe its because I didn't completely know how the aliases affected the other joins, but now it makes sense. It's a mess right now, but Take note of how I am chaining the joins together with the aliases: sections.comments becomes sections_comments, and sections.comments.replies becomes sections_comments.replies, and so on.

    query: {
      filter: {
        'comments.parentId': {
          $eq: null
        }
      },
      join: {
        package: {
          eager: true
        },
        'package.organizations': {
          eager: true
        },
        owner: {
          eager: true,
          allow: allowedUserFields
        },
        editor: {
          eager: true,
          allow: allowedUserFields
        },
        createdBy: {
          eager: true,
          allow: allowedUserFields
        },
        status: {
          eager: true
        },
        forms: {
          eager: true
        },
        'forms.fields': {
          eager: true
        },
        groups: {
          eager: true
        },
        sections: {
          eager: true
        },
        'sections.parent': {
          eager: true
        },
        'sections.lockedBy': {
          eager: true,
          allow: allowedUserFields
        },
        'sections.comments': {
          alias: 'comments',
          eager: true
        },
        'comments.owner': {
          alias: 'comments_owner',
          eager: true,
          allow: allowedUserFields
        },
        'comments.replies': {
          alias: 'comments_replies',
          eager: true
        },
        'comments_replies.owner': {
          alias: 'comments_replies_owner',
          eager: true,
          allow: allowedUserFields
        },
        'sections.versions': {
          alias: 'versions',
          eager: true
        },
        'sections.history': {
          alias: 'history',
          eager: true
        }
      }
    }

_Sorry for the length of this comment, but I am working late on some code needed for tomorrow._

[zMotivat0r]

@jeffreyschultz exactly this.
This was the exact reason I changed this function:
https://github.com/nestjsx/crud/blob/115ee2b5e315ea2869af886e4c2e3a5f7ad0b0e9/packages/crud-typeorm/src/typeorm-crud.service.ts#L447
So now it's possible not only to chain all these nested relations with aliases like you've described but also make filtering by relations and nested relations.
So I think I can close this

[jeffreyschultz]

@zMotivat0r Yeah, I was looking through TypeOrmCrudService, but am not familiar enough with its approach yet to be able to pick something like that up. Thanks for the feedback!

[zMotivat0r]

It will be covered in the documentation and in the upcoming course

[bestickley]

I've tried something similar to @jeffreyschultz with the below:

@Crud({
  dto: {
    update: UpdateUserDTO,
    create: CreateUserDTO,
  },
  model: {
    type: User,
  },
  query: {
    join: {
      org: {},
      userRoles: {},
      "userRoles.role": {
        alias: "userRoles_role",
      },
      "userRoles_role.app": {
         alias: "userRoles_role_app"
      },
    },
    filter: {
      ...deletedDateFilter,
      id: {
        $ne: 1, // filter out system user
      },
    },
  },
})

But the "userRoles_role.app" silently fails to join. I've found the issue is in this line where it looks for "userRoles_role" in the list of relations on the Role entity which doesn't exist because it's an alias. In my case, the reduce function should continue to look for "app" (the next element in "userRoles_role.app"split(".")) as the propertyName in the original relationsMetadata but instead, it looks for "app" in the relationsMetadata of "userRoles_role" which is null.
Am I doing something wrong?

P.S. Cool to see someone else on here in the Hampton Roads!

[jeffreyschultz]

Welcome, @bestickley!

Yep, I have ran into a few here on GitHub; most like yourself, over a bridge-tunnel from me, like Chesapeake or Suffolk. I see you also work for BAH. I once had a job offer from them in Charlottesville before I ended up moving back to Hampton Roads to be closer to family and friends, after starting a family of my own.

[zealotrahl]

Using aliases breaks crud-request filters

[timworld]

Hi there,

Thanks for all these explanations. I have, still, a problem even after following your guidelines. The following conf does not join table product as it should.

With this code:

@Crud({
    model: {
        type: Operation,
    },
    query: {
        join: {
            uniqueProduct: {
                eager: true,
                select: false,
            },
            'uniqueProduct.productReference': {
                eager: true,
                alias: 'uniqueProduct_productReference',
                select: false,
            },
            'uniqueProduct_productReference.product': {
                eager: true,
                alias: 'uniqueProduct_productReference_product',
                select: false,
            },
        },
    },
})

I just got (without any error):

SELECT
    "Operation"."id" AS "Operation_id", "Operation"."finished_at" AS "Operation_finished_at",
    "Operation"."created_at" AS "Operation_created_at", "Operation"."updated_at" AS "Operation_updated_at", 
    "Operation"."unique_product_id", "Operation"."instruction_id"
FROM "operation" "Operation"
LEFT JOIN "unique_product" "uniqueProduct" ON "uniqueProduct"."id"="Operation"."unique_product_id"
LEFT JOIN "product_reference" "uniqueProduct_productReference" ON "uniqueProduct_productReference"."id"="uniqueProduct"."product_reference_id"

Any idea of what I'm doing wrong? :) Thaaaanks

[timworld]

Hey back there!

I achieved what I wanted to do by rolling back from v4.6.2 to v4.5.0 and with the simple following code:

@Crud({
    model: {
        type: Operation,
    },
    query: {
        join: {
            uniqueProduct: {
                eager: true,
                select: false,
                alias: 'up',
            },
            'uniqueProduct.productReference': {
                eager: true,
                select: false,
                alias: 'pr',
            },
            'uniqueProduct.productReference.product': {
                eager: true,
                select: false,
                alias: 'p',
            },
        },
    },
})

And it works perfectly. Hope I wont need the last version for anything else :)