N+1 problem with multiple filter conditions
I have a table that maps access to "file groups" per user, for a range of time periods. each group having a different expiry date for each user.
Now I want to retrieve all files that a user has ever had access to.
for that, the naive approach would be something like
1. query the file access table: select file_group and exp_date, where user = $currentUser, order by exp_date descending, distinct per file_group, limit 1.
2. for each result pair -> query the files table: select *, where file_group = $pair.group AND created_at <= pair.exp_date
Now I know the prisma DSL has ways to mitigate this for "simple" cases where there is only 1 condition to join on, but I am not sure how I would do it with both of these conditions, as it would need to reference the result of the previous query somehow.
So I guess my question is, is there a way to do this in a single prisma client call, or am I better off manually parsing the result of the first request, collecting all the file group IDs and dates and then passing these on to the second request?
3 Replies
<bump>
<bump>
It might help to include the relevant model info. Iām kind of having a hard to groking what you are trying to do via just the queries.
sadly I cant do that š
but again: I need to query all files that a user has access to; so the naive approach is to first query all file groups + date pairs, then for each pair make another query to the files table. this very quickly causes tons of queries however so I want to avoid that