I'm struggling with a good title for this issue, so apologies. For that.

What I'm trying to achieve;

I have 3 Entities in use for this dataloader

1. User
2. Club
3. Player

• Each User can create a Club
  • User has a OneToMany Relationship with Club
  • When a User creates a Club the User id field is set as the value of the creatorId field in Club
• The User that is the creator of each Club can create a Player to assign to the Club
  • Club has a OneToMany Relationship with Player
  • When a User creates a Player at a club the Club id field is set as the value of the userId field in Player
  • A User who is a creator of a Club can link a Player to a User, the id field is set as the value of the userId field in Player

So essentially a single User can be both a creator of a Club and a Player of a Club, so can be linked to a Club by either being a creator of a player of the club.

The issue

I want a FieldResolver field called allClubs to return all Clubs in which the User is either the creator or the player of a club.

This is what I currently have

@FieldResolver(() => [Club])

@Loader<number, Club[]>(
  async (
    userIds,
    {
      context: {
        req: {
          session: { userId },
        },
      },
    }: { context: MyContext },
  ) => {
    // get all clubs where user is either creator or a player
    const clubs = await getConnection()
      .getRepository(Club)
      .createQueryBuilder('c')
      .distinct()
      .leftJoinAndMapMany('c.players', Player, 'p', 'c."id" = p."clubId"')
      .where('c."creatorId" In (:...userIds)', { userIds })
      .orWhere('p."userId" = :userId', { userId })
      .getMany();

    // group all clubs by creatorId
    const clubsById = groupBy(clubs, 'creatorId');

    return userIds.map((id) => clubsById[id] ?? []);
  },
)

allClubs(@Root() root: User): (dataloader: DataLoader<number, Club[]>) => Promise<Club[]> {
  return (dataloader: DataLoader<number, Club[]>) => dataloader.load(root.id);
}

So, I need the field to return an array of Club

I currently have the key of the DataLoader as the User id

Inside the Loader I have a query that returns all of the clubs where the user is either the creator or the player of the club. With the following structure returned

Club {
  id: 5,
  name: 'Club Name',
  creatorId: 10,
  players: [ 
    Player {
      id: 2,
      name: 'Player name',
      clubId: 5,
    },
    Player {
      id: 7,
      name: 'Player name 2',
      clubId: 5,
    } 
  ]
}

I'm not sure how to group it correctly so that when I loop over all of the provided user ids from the key that I can retrieve where the user is a player.

Currently, it's only where the user is the creator

Let's say for the above example the User id is 7, they are not the creator of the Club, but they are a Player, how would I ensure that, that Club gets returned in the Loader and not just those Clubs where they are creator?

Any ideas how I can maybe restructure the query in some way to achieve this, or any other methods to achieve the same would be very much appreciated.