I have a relationship that looks like this:

model Fighter {
  id          Int     @id @default(autoincrement())
  name        String
  image       String?
  description String?

  battles Battle[]
  votes   Vote[]
}

model Vote {
  Fighter   Fighter @relation(fields: [fighterId], references: [id])
  fighterId Int
  Battle    Battle  @relation(fields: [battleId], references: [id])
  battleId  Int
  count     Int     @default(0)

  @@id([fighterId, battleId])
}

model Battle {
  id       Int       @id @default(autoincrement())
  slug     String    @unique
  name     String
  fighters Fighter[]
  votes    Vote[]
}

A battle has multiple fighters and there is a Vote model which count the vote for each fighter in a battle. I want to retrieve a battle, include the fighters and include the vote for each fighter. I made this query:

prisma.battle.findMany({
  take: count,
  skip: skip,
  include: {
    fighters: {
      include: {
        votes: {
          select: {
            count: true
          }
        }
      }
    }
  }
});

Which solves approximately my issue because in the result a fighter has an array of votes, like this:

{
    "id": 2,
    "slug": "Random-1",
    "name": "Random 1",
    "fighters": [
        {
            "id": 3,
            "name": "1 dragon",
            "image": null,
            "votes": [
                {
                    "count": 3
                }
            ]
        },
        {
            "id": 6,
            "name": "1 hero",
            "image": null,
            "votes": [
                {
                    "count": 1
                }
            ]
        }
    ]
}

But what I would like is, for the best but I doubt it's possible:

{
  "id": 6,
  "name": "1 hero",
  "image": null,
  "votes":  1
}

To have the count of votes directly in my fighter object or at least, only one vote in the fighter object

{
  "id": 6,
  "name": "1 hero",
  "image": null,
  "votes": {
     "count": 1
  }
}

I don't know if my issue is a schema problem between my models or if I can solve it with the Prisma queries. I tried to use the include and select API from Prisma but I couldn't solve this. Does anyone have an idea about this?

CodePudding user response：

You could use _count clause which would allow you to have response similar to what you are expecting.

Here's the query:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  await prisma.battle.create({
    data: {
      name: 'Battle of the Vowels',
      slug: 'battle-of-the-vowels',
      fighters: {
        create: {
          name: 'Kabal',
          description:
            'Kabal is a fictional character in the Star Wars franchise. He is a member of the Jedi Order.',
          image:
            'https://vignette.wikia.nocookie.net/starwars/images/7/7e/Kabal_HS-SWE.png/revision/latest?cb=20170504075154',
          votes: {
            create: {
              battleId: 1,
            },
          },
        },
      },
    },
  });

  //
  // Updated Query
  //
  const battle = await prisma.battle.findMany({
    // take: count,
    // skip: skip,
    include: {
      fighters: {
        include: {
          _count: {
            select: {
              votes: true,
            },
          },
        },
      },
    },
  });

  console.log(JSON.stringify(battle, null, 2));
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Here's the sample response:

[
  {
    "id": 1,
    "slug": "battle-of-the-vowels",
    "name": "Battle of the Vowels",
    "fighters": [
      {
        "id": 1,
        "name": "Kabal",
        "image": "https://vignette.wikia.nocookie.net/starwars/images/7/7e/Kabal_HS-SWE.png/revision/latest?cb=20170504075154",
        "description": "Kabal is a fictional character in the Star Wars franchise. He is a member of the Jedi Order.",
        "_count": {
          "votes": 1
        }
      }
    ]
  }
]

Reference for using _count clause: _count prisma