Count records where number of related records is greater than X
Hey folks, I'm struggling to figure out the right way to express a particular query.
I have a model
game and a related model like. A game has 0 or more likes related to it. What I want to get: the total number of games that have at least 5 related likes. Ideally I would do this without fetching unnecessary extra data from the database.
Appreciate any help, thanks.1 Reply
You opted for speed from this bot. Let's conjure up an answer right now!
Based on the knowledge sources provided, I can answer your question about counting records where the number of related records is greater than a certain value in Prisma.
Unfortunately, Prisma does not currently support directly filtering or counting records based on the count of their related records in the way you're describing. This is a known limitation and there's an open feature request for this functionality.
The closest existing feature is the ability to count relations, but it doesn't allow filtering based on that count. For example, you can get the count of likes for each game, but you can't filter games based on that count in a single query.
There are a few workarounds you could consider, though they may not be ideal for your use case:
1. You could fetch all games with their like counts and then filter in your application code. However, this would involve fetching unnecessary data as you mentioned.
2. You could use a raw SQL query to achieve this, but that would bypass Prisma's type safety and abstraction.
3. You could potentially use Prisma's aggregation features to get close to what you want, but it would still require some post-processing in your application code.
For the most up-to-date information on this feature request and potential workarounds, you can check the following GitHub issue:
Search and filter by relation count, with
eq, gt, lt etc.
If this feature is crucial for your application, you might want to consider adding your use case to this issue and giving it a thumbs up to help prioritize its development.