prisma
count() support distinct
Problem
I have bumped into a few cases where I need to do a 'where' filter and get records that are 'distinct', to add pagination I need to do a count query also with a 'distinct' option on 'count()', based on the documentation and experience, I can't use distinct on a count()
export type FindManyUserArgs = {
where?: UserWhereInput | null
orderBy?: Enumerable<UserOrderByInput> | null
skip?: number | null
after?: UserWhereUniqueInput | null
before?: UserWhereUniqueInput | null
first?: number | null
last?: number | null
}
Suggested solution
Can we update count() to support distinct as well? so it would accept the object similar to findMany()
export type FindManyUserArgs = {
select?: UserSelect | null
include?: UserInclude | null
where?: UserWhereInput | null
orderBy?: Enumerable<UserOrderByInput> | null
cursor?: UserWhereUniqueInput | null
take?: number | null
skip?: number | null
distinct?: Enumerable<UserDistinctFieldEnum>
}
Oddly, the TypeScript type for count this doesn't omit distinct - it just omits include and overwrites select, so there is no type error if you try to write something like prisma.myModel.count({ distinct: ['userId'] })
type MyModelCountArgs = Merge<
Omit<MyModelFindManyArgs, 'select' | 'include'> & {
select?: MyModelCountAggregateInputType | true
}
>
Oddly, the TypeScript type for
countthis doesn't omitdistinct- it just omitsincludeand overwritesselect, so there is no type error if you try to write something likeprisma.myModel.count({ distinct: ['userId'] })type MyModelCountArgs = Merge< Omit<MyModelFindManyArgs, 'select' | 'include'> & { select?: MyModelCountAggregateInputType | true } >
Yeah noticed that aswell. Although any query with distinct does fail.
Any progress on this? I really need to count the number of unique occurrences in a column.
For folks who need this, here's my workaround until it's supported:
async function getReaderCount() {
// couldn't figure out how to do this in one query without $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
}
While the workaround indeed...works, it would be great to have a fix on this as the documentation says that it should function correctly.
Can you point us to the misleading documentation please @deekerno? That should of course not be the case while we wait for the feature to be implemented.
Correct me if i am wrong @deekerno but the misleading documentation is here in the api reference https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#count where it says that count has an option called distinct
@janpio, @stefanoruth is correct with the location of the documentation. Thanks!
Would be happy to use prisma I just couldn't do the DISTINCT bit here
const countCandidatesAddedToListPromise = this.prisma.$queryRaw<
{
ownerId: string
count: number
}[]
>(Prisma.sql`
SELECT
"ownerId",
COUNT(DISTINCT "candidateId") as "count"
FROM "CandidateListMap"
WHERE "createdAt" BETWEEN ${timeRange.start} AND ${timeRange.end}
GROUP BY "ownerId"
ORDER BY count ${Prisma.raw(sortOrder)}
`)
This feature is needed. Whats weird is that the documentation on Prisma is wrong it says count() supports distinct but the error returns:
Unknown arg distinctin distinct for type AggregateProduct. Did you meanselect? Available args: type aggregateproduct { where?: productWhereInput orderBy?: List<productOrderByWithRelationInput> | productOrderByWithRelationInput cursor?: productWhereUniqueInput take?: Int skip?: Int }
I guess the only way to support this feature is first to implement real distinct SQL queries (see #14765). Currently distinct is done programmatically, which I guess is not an option for count.
凸pirsma凸 an expensive method
const userList = await this.prismaRead.user.groupBy({
by: ['name'],
where: name: {
contains: name,
},
});
const count = userList.length;
I would also need this feature. It's strange that it's allowed to use distinct in count by TypeScript but it fails at runtime. You could at least fix the types if you don't have enough resources to make this feature work.
Here's my workaround using findMany with distinct:
export async function countUniqueVotes() {
const results = await prisma.vote.findMany({
distinct: ['accountId'],
select: {
accountId: true,
},
});
return results.length;
}
But I'm a little bit afraid of using it on large tables.
Sadly $queryRaw is really the only answer for this currently (and a ton of other feature request tickets). See the sample from @revmischa above for an example of how that'd work.
Prisma crew - there are a ton of features like this which you guys don't have implemented and encountering one suddenly forces you to jump back to raw queries. Is there some big catch-up project to handle them? Count distinct is hardly a niche use case and it's staggering that the framework can't support it along with many other standard operations and aggregations.
Initially I'd tried to use native Prisma functions for everything, but at this point my API is something like 95% raw queries due to how many restrictions I've hit. That's a big bummer and I wonder if it's indicative of how things will be in the future. I can't even use automated migrations due to how many features are unsupported there as well. Maybe some big disclaimers would be in order for onboarding guides about how Prisma isn't intended for anyone doing anything resembling analytics?
