TypeORM multiple on conditions for join clause

Question

I am working on a project that uses TypeORM and PostgreSQL, I am trying to use the query builder to join on multiple conditions. Is there an easier/ more programmatic way to do this than having all the conditions within a string? For example, I want to build the following query to get friends for a user. In SQL this query looks like this (Note: inRel is short for incoming relationship and outRel is short for outgoing relationship)

-- SELECT FRIENDS FOR USER
select outRel."relatingToUserId"
from relationships outRel 
inner join relationships inRel 
    on inRel."userId" = outRel."relatingToUserId"
    and inRel."relatingToUserId" = outRel."userId"
    and inRel."type" = 'FRIEND'
    and outRel."type" = 'FRIEND'
where outRel."userId" = 'some_uuid_for_the_user';

In TypeORM I can accomplish the same result doing

const relationships = await this.createQueryBuilder()
  .select('outRel.relatingToUserId')
  .from(RelationshipEntity, 'outRel')
  .innerJoin(
    RelationshipEntity,
    'inRel',
    `
      inRel.userId = outRel.relatingToUserId
      AND inRel.relatingToUserId = outRel.userId
      AND inRel.type = 'FRIEND'
      AND inRel.type = outRel.type
    `,
  )
  .where('outRel.userId = :userId', { userId })
  .getMany();

However, I would expect that I should be able to do something more like

const relationships = await this.createQueryBuilder()
  .select('outRel.relatingToUserId')
  .from(RelationshipEntity, 'outRel')
  .innerJoin(RelationshipEntity, 'inRel', 'inRel.userId = outRel.relatingToUserId')
  .andWhere('inRel.relatingToUserId = outRel.userId')
  .andWhere("inRel.type = 'FRIEND'")
  .andWhere('inRel.type = outRel.type')
  .where('outRel.userId = :userId', { userId })
  .getMany();

But this does not return the same result. Is there a way to build this query more programmatically or am I stuck with a query string?

Answer 1

andWhere are used after .where. Try this:

const relationships = await this.createQueryBuilder()
.select('outRel.relatingToUserId')
.from(RelationshipEntity, 'outRel')
.innerJoin(RelationshipEntity, 'inRel', 'inRel.userId = outRel.relatingToUserId and inRel.relatingToUserId = outRel.userId and inRel.type = outRel.type')
.where('outRel.userId = :userId', { userId })
.andWhere('inRel.type = 'FRIEND'')
.getMany();