CODEWITHSUNDEEP
node.jspostgresqltypeorm
Albert
Albert

Reputation: 2664

Typeorm gives me QueryFailedError: column reference "id" is ambiguous

This is the query I'm firing off:

const updatedUser = await queryRunner.manager
    .getRepository(UserEntity)
    .createQueryBuilder('user')
    .leftJoinAndSelect('user.categories', 'cats')
    .where('id = :userId', { userId })
    .getOne();

And here is the query that is generated by typeorm:

SELECT "user"."id"               AS "user_id", 
       "user"."first_name"       AS "user_first_name", 
       "user"."last_name"        AS "user_last_name", 
       "user"."phone"            AS "user_phone", 
       "user"."password"         AS "user_password", 
       "user"."password_hint"    AS "user_password_hint", 
       "user"."recovery_email"   AS "user_recovery_email", 
       "user"."created"          AS "user_created", 
       "user"."username"         AS "user_username", 
       "user"."profile_pic"      AS "user_profile_pic", 
       "user"."is2fa"            AS "user_is2FA", 
       "user"."refresh_token"    AS "user_refresh_token", 
       "user"."profile_email"    AS "user_profile_email", 
       "user"."subscriptionid"   AS "user_subscriptionId", 
       "cats"."id"               AS "cats_id", 
       "cats"."template_id"      AS "cats_template_id", 
       "cats"."name"             AS "cats_name", 
       "cats"."entry_count"      AS "cats_entry_count", 
       "cats"."is_base_template" AS "cats_is_base_template", 
       "cats"."can_rename"       AS "cats_can_rename", 
       "cats"."can_delete"       AS "cats_can_delete", 
       "cats"."userid"           AS "cats_userId", 
       "cats"."position"         AS "cats_position", 
       "cats"."icon_path"        AS "cats_icon_path", 
       "cats"."init_name"        AS "cats_init_name", 
       "cats"."isfortasks"       AS "cats_isForTasks", 
       "cats"."is_locked_by"     AS "cats_is_locked_by", 
       "cats"."is_locked"        AS "cats_is_locked", 
       "cats"."password"         AS "cats_password", 
       "cats"."state"            AS "cats_state", 
       "cats"."password_hint"    AS "cats_password_hint", 
       "cats"."parentcategoryid" AS "cats_parentCategoryId" 
FROM   "user" "user" 
       LEFT JOIN "category" "cats" 
              ON "cats"."userid" = "user"."id" 
WHERE  id = $1

If I remove the leftJoinAndSelect it doesn't yield that error but I need this join.

What is wrong with the query?

META:

The DBMS I'm using is PostgreSQL v. 12.3.

Upvotes: 2

Views: 5869

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

Well, as you can see there is a user.id and a cats.id available - you even select both. But in the WHERE clause you don't say which id you mean. Change 'id = :userId' to 'user.id = :userId' and it should work.

Upvotes: 10

Related Questions