How to join one-to-zero-or-one relation?

Question

I am using node.js typeorm and I have 2 entities with one-to-zero-or-one relation as follows:

@Entity('issuer')
export class Issuer {
  @PrimaryGeneratedColumn()
  issuerId: number

  @OneToOne(() => User)
  @JoinColumn({ name: 'userId' })
  user: User
  
  ...remaining columns...
}

@Entity('user_data')
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @Column({ type: 'varchar', nullable: false, unique: true })
  email: string

  ...remaining columns...
}

A user might be issuer or not, but an issuer certainly is user.

What I am trying to achieve is that without knowing the user type, I want to join 2 tables. For instance considering the following database rows:

User  | id | email | 
--------------------
user1 | 1  | xx@xx |
--------------------
user2 | 2  | yy@yy |


Issuer  | issuerId | userId |
----------------------------
issuer1 |    1     |    1   | 

When I query the email xx@xx I want to get:

id: 1, email: xx@xx, issuerId: 1

When I query the email yy@yy I want to get:

id: 2, email: yy@yy, issuerId: NULL

or it is even ok for issuerId to be dropped if NULL.

I tried the following query:

SELECT *
FROM user_data
LEFT JOIN issuer ON issuer.userId = user_data.id
WHERE user_data.email = $1

where $1 is email parameter, but I get the following error:

"severity": "ERROR",
"code": "42703",
"hint": "Perhaps you meant to reference the column \"issuer.userId\".",
"file": "parse_relation.c",
"routine": "errorMissingColumn"

What am I doing wrong, and what can I do to achieve my goal?

Answer 1

It seems that when using camelcase column names, they should be wrapped with double quotes. And also instead of LEFT JOIN, I should have used LEFT OUTER JOIN.

The corrected query looks like this:

SELECT *
FROM user_data
LEFT OUTER JOIN issuer ON issuer."userId" = user_data.id
WHERE user_data.email = $1