Prisma - How to use count as a where condition with relation

Question

I use nestjs and postgresql with prisma. I have 2 tables in relation, I want to create a where clause in order to fetch the records if count of the records in the second table is less than -let's say- 3. More details;

Here is my schema

model User {
  id                      String            @id
  someOtherFields         String
  outgoingPlayMateRequest PlayMateRequest[] @relation("userId")
  incomingPlayMateRequest PlayMateRequest[] @relation("targetId")
}

model PlayMateRequest {
  id               Int      @id
  requestingUser   User     @relation(name: "userId", fields: [requestingUserId], references: [id], onDelete: Cascade)
  targetUser       User     @relation(name: "targetId", fields: [targetUserId], references: [id], onDelete: Cascade)
  requestingUserId String
  targetUserId     String
  someOtherFields  String
  response         String   //accept-reject-block
}

and here is my code with where clause (I am simplfying it by removing unrelevant parts)

const userId = 'testUser';
return await this.prismaService.user.findMany({
    where: {
      NOT: {
        id: userId //don't fetch user him/herself
      },
      lang: 'EN',
    }
  });

The condition I want to add here in english is;

Don't select users with incomingPlayMateRequest relation, if there are 3 records in PlayMateRequest table with response = reject AND requestingUser = userId

But I couldn't find anyway to use count as a condition in where. As I see I can only get the relations count. How can I do this with prisma?

Answer 1

There's no direct way to do a condition like this in Prisma, but here is a workaround you could use:

1. Do a groupBy query for the filter condition.
2. Use a map to create array of User.id fields of all the user ids that match the filter condition.
3. Do the normal findMany query, but add an extra notIn condition with the array of filtered user ids.

Here is what the whole thing would look like:

const userId = 'testUser';

// step 1
const dataForFilter = await prisma.playMateRequest.groupBy({
    by: ['targetUserId'],
    where: {
        response: "reject",
        requestingUserId: userId
    },
    having: {
        targetUserId: {
            _count: {
                equals: 3  
            }
        }
    }
})

// step 2
let exclude_users = [userId]
exclude_users = exclude_users.concat(dataForFilter.map(item => item.targetUserId))

let result = await prisma.playMateRequest.user.findMany({
    where: {
        id: {
        notIn: exclude_users
        },
        lang: "en"
    }
    });

I might have misinterpreted the exact details of the query you want to achieve but I think this should give you the query structure in general. Tweak the groupBy query as necessary to match your exact condition.