best practice to load many relations ids from database

[vincentlejeune]

I try to implement a solution that uses graphql-java and dataloader over a database.

I would like to know the best pratice to load a many relations when the nested ids required a database query.

in the documentation example, imagine that we replace starWarsCharacter.getFriendsIds() by a database query getFriendIdsFromDB(starWarsCharacter).
The friendsDataFetcher would looks like :

 DataFetcher friendsDataFetcher = new DataFetcher() {
            @Override
            public Object get(DataFetchingEnvironment environment) {
                StarWarsCharacter starWarsCharacter = environment.getSource();
                List<String> friendIds = getFriendIdsFromDB(starWarsCharacter);
                return characterDataLoader.loadMany(friendIds);
            }
        };

In this case it generates again a N+1 fetch problem. For each Character we have to query for the friends ids.

How to efficiently manage that case with the dataloader?

[bbakerman]

Really what you are doing here is getting the "friends ids" of the character AND then loading those friends. So we need a batch loader that does those 2 operations at the same time.

So I think you might want to combine them into one batch loader that takes a "characterID" and can find friends for them all in the one step

So imagine your batch loader is like this (psudeo java code)

BatchLoader bl  = characterIdsKeys -> {
   
     ResultSet rs = db.execute(' select from friends where friends.characterId in ::characterIdsKeys:: group by friends.characterId');

   // now run through the group set of friends per characterId and make Character objects from them
  return serviceToUnpackGroupedResultSet(rs)
}

Now it might be that your database tables can only "find the ids of friends" in one query and then find the friends themselves via another query. So be it. Use 2 queries. Use the SQL "in" operator to find results for multiple elements (eg the batch of characters). Use SQL groupBy or order by to make sure you can pack back the results into a list that matches the input keys

[vincentlejeune]

Hi @bbakerman

Thanks for your reply. I create a dataloader for the relation between characterId and the list of Friend. It works like a charm !

Just to be sure to understand, to load friends by characterIds, i create a dataloader that store the list of friends by characterId (DataLoader<CharacterId, List<Friend>>). So, by doing this, i by pass a potential Friend's dataloader (DataLoader<String, Friend>).

Is it possible to load the friends id and next use a Friend's dataloader to get the Friends, all of that in an async way ?

[budjb]

This issue actually looks like the same thing I'm asking about in #36. @bbakerman, do you have an example of how the batch loader you pseudo-coded above would be used? This differs from the examples, since in the examples the IDs of the objects you want to load are passed to the dataloader, whereas here it seems you need to pass the ID of the source object. I'm not sure how that should look in usage.

[Sam-Kruglov]

@bbakerman Hey, I'm trying to model a many-to-many, similar to this discussion and getting DataLoaderAssertionException: The size of the promised values MUST be the same size as the key list (DataLoaderHelper.java:375)

I'm trying to do List<FriendId> friendIds = friendIdLoader.load(characterId); charecterLoader.loadMany(friendIds);

Any advise on how to fetch that properly?

[bbakerman]

Maybe org.dataloader.MappedBatchLoader - its returns a map which allows less results than the asked for set of ids.

I am not sure which of those calls above in the snippets is failing.