Why Sub query in leftjoin in typeorm giving me error?

Question

If i am defining innerjoin or leftjoin in subquery of leftjoinandselect, then it is giving me error sqlMessage: "Duplicate column name 'id'".

For example

.leftJoinAndSelect(
            (qb) =>
              qb
                .from(Email, 'email')
                .innerJoin(User, 'user')
                .where('user.account = :account', {
                  accountId: 4,
                }),
            'email',
            '`email`.`id` = 1',
          )

The above will give me error "Duplicate column name 'id'"

But if i remove innerJoin then it is working fine and giving expected result.

.leftJoinAndSelect(
                (qb) =>
                  qb
                    .from(Email, 'email'),
                'email',
                '`email`.`id` = 1',
              )

Any idea why is it giving me this error.

Answer 1

I think the problem is that both email and user tables have id columns. Can you try the following code?:

.leftJoinAndSelect(
            (qb) =>
              qb    
                .select("user.id", "user_id")
                .addSelect("email.id", "email_id")
                // Add whatever you want by .addSelect method
                .from(Email, 'email')
                .innerJoin(User, 'user')
                .where('user.account = :account', {
                  accountId: 4,
                }),
            'email',
            '`email`.`id` = 1',
          )