2

I have a DB with 2 tables => Player (10k entries) and Goal (100k entries). I have a relationship one to many between them (Player has many Goals)

@Entity()
@ObjectType()
class Player {
  @Field(() => ID)
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Field(() => [Goal])
  @OneToMany(
    () => Goal,
    goal => goal.player
  )
  goals: Goal[];
  ...
}

@Entity()
@ObjectType()
class Goal {
  @Field(() => ID)
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @ManyToOne(
    () => Player,
    player => player.id
  )
  player: Player;
  ...
}

I want to query players and their goals in order to display players rankings in my client app

query scorers{
  players{
    id
    totalGoals
  }
}

Player resolver looks like this

@Resolver(Player)
class PlayerResolver {
  @Query(() => [Player])
  async players() {
    return this.playerRepository.createQueryBuilder("player").getMany();
  }
  @FieldResolver()
  async totalGoals(@Root() player: Player) {
    return this.goalRepository.count({ player });
  }
}

This is not performant because it creates a SQL query behind the scene to request the goals for every Player. So it's potentially 10k queries. Is it the best strategy to do this? Also I'm wondering how I could possibly order the players by goals count. I'm using type-graphql and typeorm.

elyrico
  • 519
  • 6
  • 19
  • cache totalGoals as a field/prop in player, shouldn't be counted on [reading] resolver ... update on goal instert – xadm Dec 23 '20 at 14:43
  • 1
    see this https://stackoverflow.com/questions/63663128/best-way-to-handle-one-to-many-with-type-graphql-typeorm-and-dataloader – Vitor Piovezam Dec 23 '20 at 14:58
  • Querying 10k players at once seems to be the actual problem imo. – Bergi Dec 23 '20 at 15:14
  • @xadm is it something common to create cache field/prop in SQL database? Do you references about that? – elyrico Dec 24 '20 at 09:03
  • it's frequently used and costly ... how do you sort players by totalGoals ... in a season? complexity grows exponentialy – xadm Dec 24 '20 at 13:31
  • @xadm I sort player by totalgoals in a season! Also I have to sort players by minutes played, yellow/red cards and other individual stats. Having cache field/prop for each of stat seems to be the most performant solution from a user point of view. When I run my query threw my joins it can take until 10 seconds to respond :-/ – elyrico Dec 24 '20 at 14:41
  • ...and don't you think that you should have this data [stats] at least precalculated [in separate table] ... any DB design knowledge? DB [materialized] views ? – xadm Dec 24 '20 at 20:11
  • I have limited DB knowledge. I designed relationships between my tables but no views nor materialized views. I'm gonna explore this topic. Thank you @xadm – elyrico Dec 26 '20 at 08:44

1 Answers1

0

You can either try to build the query with join ahead of time (on query resolver level, using @Info to read requested fields) or use a DataLoader library to batch the queries and solve the N + 1 problem.

Michał Lytek
  • 10,577
  • 3
  • 16
  • 24