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.
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.