How to use leftJoinAndSelect query in TypeORM postgres?

Question

I've been using TypeORM and made some Entities as follows:

• User.ts

  @PrimaryGeneratedColumn()
  id: number
  

• Post.ts

  @PrimaryGeneratedColumn()
  id: number
  
  @Column()
  userId: number
  

As I didn't want to make relations between User and Post entities, I just made a column named userId at Post.

Here's the question.

I'd like to join two tables and fetch data. But how can I use innerJoinAndSelect or leftJoinAndSelect?

Here's my code:

// getPost.ts

// Attempt 1
const first = await createQueryBuilder()
  .select('user')
  .from(User, 'user')
  .leftJoinAndSelect(Post, 'post', 'post.userId = user.id')
  .getMany() // only fetched User data without Post data

// Attempt 2
const second = await createQueryBuilder('user')
  .innerJoinAndSelect('user.id', 'userId')
  .innerJoinAndMap('user.id', Post, 'post', 'userId = post.userId')
  .getMany() // Bad request error

But None of these worked...

I'd like to join User with Post and grab all data (but not using relation). How could I get these data?

Answer 1

leftJoinAndMapOne should work. But you’ve just omitted first argument mapToProperty.

Just fix your getPost.ts as this

const first = await createQueryBuilder()
  .select(‘user’)
  .from(User, ‘user’)
  // ------------------------ fixed ------------------------
  .leftJoinAndMapOne(‘user.id’, Post, ‘post’, ‘post.userId = user.id’)
  // -------------------------------------------------------
  .getMany()

Answer 2

Since you don't want to create foreign key relations in the database, but still would like to use join operations on the entities, you can use createForeignKeyConstraints property in the relation options to achieve this.

Update your entities as below:

User.ts

@PrimaryGeneratedColumn()
id: number

@OneToMany(() => Post, (post) => post.user)
posts: Post[]

Post.ts

@PrimaryGeneratedColumn()
id: number

@Column()
userId: number

@ManyToOne(() => User, (user) => user.posts, { createForeignKeyConstraints: false })
@JoinColumn({ name: 'userId' })
user: User

Now you should be able to use find or QueryBuilder methods on these entities just as they had an actual relationship in the database.

---

I didn't test this code but this is the essence of what you should be doing. Hope this helps. Cheers 🍻 !!!