How to join a table in typeORM with innerJoinAndSelect service

Question

Entity -pin

@Entity()
export class Pin {
  @PrimaryGeneratedColumn()
  @PrimaryColumn()
  id: string;

  @Column({name: 'town_name'})
  townName: string;

  @Column()
  state_id: string;

  @ManyToOne(() => State, state => state.stateId)
  state: State;
}

Entity -state

@Entity()
export class State {

  @PrimaryGeneratedColumn({name: 'state_id'})
  @PrimaryColumn()
  stateId: string;

  @Column({name: 'state_name'})
  stateName: string;

}

Service:

const pin = pinRepository.createQueryBuilder('pin').leftJoinAndSelect('pin.state','state').getQuery();
    console.log(pin);

I'm new with nest and typeORM and try to join the two table. but it's returning wrong query:

SELECT "pin"."id" AS "pin_id","pin"."town_name" AS "pin_town_name", "pin"."state_id" AS "pin_state_id", "pin"."stateStateId" AS "pin_stateStateId", "state"."stateId" AS "state_stateId", "state"."state_name" AS "state_state_name" FROM "pin" "pin" LEFT JOIN "state" "state" ON "state"."stateId"="pin"."stateStateId"

While I would want something queries like this:

select id, town_name, Pin.state_id, state_name from Pin inner join State ON Pin.state_id = State.state_id

In which part that I've passed the wrong thing? or is there any way I can overcome this problem.

Answer 1

I'm not exactly sure what are the issues you're struggling with but the following should work. I think the problem is in the entities relation definition.

Take a look at @JoinColumn

// pin.entity.ts

@Entity()
export class Pin {
  @PrimaryGeneratedColumn()
  id: string;

  @Column({name: 'town_name'})
  townName: string;

  @Column()
  state_id: string;

  @ManyToOne(() => State, state => state.pins)
  @JoinColumn({ name: 'state_id' })
  state: State;
}

// state.entity.ts

@Entity()
export class State {

  @PrimaryGeneratedColumn({name: 'state_id'})
  stateId: string;

  @Column({name: 'state_name'})
  stateName: string;

  @OneToMany(() => Pin, pin => pin.state)
  pins: Pin[]

}

Also, change left to inner join.

const pin = await pinRepository.createQueryBuilder('pin')
.innerJoinAndSelect('pin.state','state')
.getMany()