Skip to content
Advertisement

Typeorm Postgres select where double nested relation is equal to value

I have a relation like the following:

Match
id
teams
@OneToMany(() => Team, (team) => team.match)
  teams: Team[];
teams
id
players
 @OneToMany(() => Player, (player) => player.team)
  players: Player[];

@ManyToOne(() => Match)
  match: Match;
players
id
name
@ManyToOne(() => Player, { nullable: true })
  player: Player;

  @ManyToOne(() => Team)
  team: Team;

From here I want to select the match that has the player name ‘x’.

Currently I have the following:

const matchDataQuery = getRepository(Match)
              .createQueryBuilder('match')
              .select('match.id')
              .leftJoinAndSelect('match.teams', 'teams')
              .leftJoinAndSelect('teams.players', 'players')
              .where('players.name = :name', { name: player.name })

But this returns only a single team and single player that matches my where.

{
  id: 1,
  teams: [
    {
      id: 'team1',
      players: [
        {
          id: 'player1',
           name: 'x'
        }
      ]
    }
  ]
}

This match of id: 1 actually has more than 1 team and in each team there is more than 1 player but it only returns a single entity of each.

I need it to return all teams and all players in the match which has this player playing in it. How can I do this?

Thanks!

Advertisement

Answer

I was able to solve my problem by using a subquery

const matchDataQuery = getRepository(Match)
              .createQueryBuilder('match')
              .leftJoinAndSelect('match.teams', 'teams')
              .leftJoinAndSelect('teams.players', 'players')
              .where((qb) => {
                const subQuery = qb
                  .subQuery()
                  .select('match.id')
                  .from(Match, 'match')
                  .leftJoin('match.teams', 'teams')
                  .leftJoin('teams.players', 'players')
                  .where('players.name = :name')
                  .getQuery();
                return 'match.id IN ' + subQuery;
              })
              .setParameter('name', 'WhateverName')
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement