What s the easiest way to do a select distinct with a group Prisma #orm-help

What's the easiest way to do a select distinct wit...

Kent C. Dodds

08/19/2021, 8:17 PM

What's the easiest way to do a select distinct with a group by? Here's my model:

Copy code

model PostRead {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  user      User?    @relation(fields: [userId], references: [id])
  userId    String?
  clientId  String?
  postSlug  String
}

I want to be able to get a count of distinct rows by userId and clientId (if the user isn't logged in, then I have a randomly generated ID for their client). I've tried this:

Copy code

prisma.postRead.groupBy({
  _count: true,
  by: ['userId', 'clientId'],
})

But that returns an array for each group with

_count

which I can then add manually, but I'd prefer to do that in the db. I've also tried

count

, but I'm not sure what to put in my query... And I looked at aggregate but I can't figure out what I'd put in there either 🤔

Kent C. Dodds

08/19/2021, 8:19 PM

Basically, I'm trying to figure out how to count the total number of people have read all the blog posts.

Kent C. Dodds

08/19/2021, 8:42 PM

Here's the workaround I've got for now:

Copy code

async function getReaderCount() {
  // couldn't figure out how to do this in one query with out $queryRaw 🤷‍♂️
  type CountResult = [{count: number}]
  const [userIdCount, clientIdCount] = await Promise.all([
    prisma.$queryRaw`SELECT COUNT(DISTINCT "public"."PostRead"."userId") FROM "public"."PostRead" WHERE ("public"."PostRead"."userId") IS NOT NULL` as Promise<CountResult>,
    prisma.$queryRaw`SELECT COUNT(DISTINCT "public"."PostRead"."clientId") FROM "public"."PostRead" WHERE ("public"."PostRead"."clientId") IS NOT NULL` as Promise<CountResult>,
  ]).catch(() => [[{count: 0}], [{count: 0}]])
  return userIdCount[0].count + clientIdCount[0].count
}

If anyone can help me figure out how to improve that, I would welcome the help 🙂

👀 1

Ryan

08/20/2021, 6:27 AM

@Kent C. Dodds 👋 Unfortunately count with distinct isn’t supported yet. It would be great if you could add a 👍 to this feature request so that we can know the priority 🙂 The workaround would have to be a raw query for now as you have created above.

👍 1

Open in Slack

Previous Next