CODEWITHSUNDEEP
sqlprisma
Danila
Danila

Reputation: 18536

Find entity with most relations filtered by criteria

model Player {
  id   String @id
  name String @unique
  game Game[]
}

model Game {
  id       String   @id
  isWin    Boolean
  playerId String
  player   Player   @relation(fields: [playerId], references: [id])
}

I would like to find a player with most wins. How would I do that with prisma? If there is no prisma "native" way to do it, what is the most efficient way to do this with raw SQL?

The best I could think of is:

    prisma.player.findMany({
      include: {
        game: {
          where: {
            isWin: true,
          },
        },
      },
    })

But it has huge downside that you need to filter and order results in Node manually, and also store all results in memory while doing so.

Upvotes: 1

Views: 209

Answers (1)

Tasin Ishmam
Tasin Ishmam

Reputation: 7268

Using the groupBy API you can find the player with the most wins using two queries.

1. Activate orderByAggregateGroup

You'l need to use the orderByAggregateGroup preview feature and use Prisma version 2.21.0 or later.

Update your Prisma Schema as follows

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["orderByAggregateGroup"]  
}
// ... rest of schema

2. Find playerId of most winning player

Use a groupBy query to do the following:

  1. Group games by the playerId field.
  2. Find the count of game records where isWin is true.
  3. Order them in descending order by the count mentioned in 2.
  4. Take only 1 result (since we want the player with the most wins. You can change this to get the first-n players as well).

The combined query looks like this:

 const groupByResult = await prisma.game.groupBy({
        by: ["playerId"],
        where: {
            isWin: true,
        },
        _count: {
            isWin: true,
        },
        orderBy: {
            _count: {
                isWin: "desc",
            },
        },
        take: 1,   // change to "n" you want to find the first-n most winning players. 
    });

    const mostWinningPlayerId = groupByResult[0].playerId;

I would suggest checking out the Group By section of the Aggregation, grouping, and summarizing article in the prisma docs, which explains how group by works and how to use it with filtering and ordering.

3. Query player data with findUnique

You can trivially find the player using a findUnique query as you have the id.

const mostWinningPlayer = await prisma.player.findUnique({
        where: {
            id: mostWinningPlayerId,
        },
    });

Optionally, if you want the first "n" most winning players, just put the appropriate number in the take condition of the first groupBy query. Then you can do a findMany with the in operator to get all the player records. If you're not sure how to do this, feel free to ask and I'll clarify with sample code.

Upvotes: 3

Related Questions