TypeOrm/PostgreSQL createQueryBuilder: select column from joined table

user_3521253 0
Viewed 56

I want to select only a column from a table that I'm joining into. A separator has a facility, a facility has a customer, and I want to select customer.id by providing a separatorId.

Below is what I have tried, but this returns a Separator.

return await this.separatorRepository
  .createQueryBuilder('separator')
  .select(['customer.companyId'])
  .innerJoin('separator.facility', 'facility')
  .innerJoin('facility.customer', 'customer')
  .where('separator.id = :separatorId', { separatorId })
  .getOne();

How do I change this query to get customer.id?

typeorm nestjs postgresql
1 Answers

I suppose your joined tables are not selected from because you use innerJoin method instead of innerJoinAndSelect. Additionally, since getOne returns objects with your entity type (Separator in your case), you need to make sure the relations between Separator, Facility, and Customer are properly specified.

Entities:

@Entity()
export class Customer {
    @PrimaryColumn()
    id: number;
}

@Entity()
export class Facility {
    @PrimaryColumn()
    id: number;

    @OneToOne(() => Customer)
    @JoinColumn()
    customer: Customer;
}

@Entity()
export class Separator {
    @PrimaryColumn()
    id: number;

    @OneToOne(() => Facility)
    @JoinColumn()
    facility: Facility;
}

Query:

return await this.separatorRepository
  .createQueryBuilder('separator')
  .innerJoinAndSelect('separator.facility', 'facility')
  .innerJoinAndSelect('facility.customer', 'customer')
  .where('separator.id = :separatorId', { separatorId })
  .getOne();

The query yields you the instance of Separator with the joined entities, so you could get your customer.id from there.

user_57167450
Related