TypeORM: How to get the one to one relationship on a query that has multiple relationships

Question

I have the following query

const user = await userRepository
        .createQueryBuilder('user')
        .leftJoin('user.roles', 'role')
        .leftJoin('user.storage', 'storage')
        .leftJoin('user.transferHistory', 'transferHistory')
        .leftJoin('user.governmentLeaderOf', 'governmentLeaderOf', 'user.governmentLeaderOf = governmentLeaderOf.governmentLeader')
        .select(['user', 'role', 'storage', 'transferHistory', 'governmentLeaderOf'])
        .where({ id: id })
        .getOne();

and TypeORM's auto generated query has a typo:

query: 'SELECT "user"."id" AS "user_id", "user"."username" AS "user_username", "user"."email" AS "user_email", "user"."password" AS "user_password", "user"."level" AS "user_level", "user"."experience" AS "user_experience", "user"."strengthPerk" AS "user_strengthPerk", "user"."dexterityPerk" AS "user_dexterityPerk", "user"."educationPerk" AS "user_educationPerk", "user"."isPerkActive" AS "user_isPerkActive", "user"."vip" AS "user_vip", "user"."money" AS "user_money", "user"."gold" AS "user_gold", "user"."stamina" AS "user_stamina", "user"."realNation" AS "user_realNation", "user"."perkLastUpdate" AS "user_perkLastUpdate", "user"."isFirstAccess" AS "user_isFirstAccess", "user"."ipAddress" AS "user_ipAddress", "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."jobId" AS "user_jobId", "role"."id" AS "role_id", "role"."name" AS "role_name", "role"."description" AS "role_description", "role"."createdAt" AS "role_createdAt", "role"."updatedAt" AS "role_updatedAt", "storage"."id" AS "storage_id", "storage"."name" AS "storage_name", "storage"."description" AS "storage_description", "storage"."unitPrice" AS "storage_unitPrice", "storage"."quantity" AS "storage_quantity", "storage"."image" AS "storage_image", "storage"."category" AS "storage_category", "storage"."userId" AS "storage_userId", "transferHistory"."id" AS "transferHistory_id", "transferHistory"."transferType" AS "transferHistory_transferType", "transferHistory"."transferValue" AS "transferHistory_transferValue", "transferHistory"."transferredTo" AS "transferHistory_transferredTo", "transferHistory"."transferDate" AS "transferHistory_transferDate", "transferHistory"."userId" AS "transferHistory_userId", "governmentLeaderOf"."id" AS "governmentLeaderOf_id", "governmentLeaderOf"."name" AS "governmentLeaderOf_name", "governmentLeaderOf"."logo" AS "governmentLeaderOf_logo", "governmentLeaderOf"."population" AS "governmentLeaderOf_population" FROM "user" "user" LEFT JOIN "user_roles_role" "user_role" ON "user_role"."userId"="user"."id" LEFT JOIN "role" "role" ON "role"."id"="user_role"."roleId"  LEFT JOIN "storage" "storage" ON "storage"."userId"="user"."id"  LEFT JOIN "transfer_history" "transferHistory" ON "transferHistory"."userId"="user"."id"  LEFT JOIN "government" "governmentLeaderOf" ON  AND (user.governmentLeaderOf = governmentLeaderOf.governmentLeader) WHERE "user"."id" = @0'

and the error is: driverError: RequestError: Incorrect syntax near the keyword 'AND'.

if you notice at the end of the query, it has a LEFT JOIN "government" "governmentLeaderOf" ON AND (user.governmentLeaderOf = governmentLeaderOf.governmentLeader) with a typo on the ON AND... It's the second time I run into this issue using TypeORM and MS SQL, the workaround I did the other time was using OneToMany on this issue (this only happens when using OneToOne relationship, Idk why but I feel like this is a MS SQL specific integration error but this is my a** talking xD)

The relationship on the entities:

User entity:

@OneToOne(() => Government, government => government.governmentLeader, {onDelete: 'CASCADE'})
governmentLeaderOf : Government

Government Entity:

@OneToOne(() => User, user => user.governmentLeaderOf)
governmentLeader : User

TypeORM: How to get the one to one relationship on a query that has multiple relationships

0 Answers0