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')