Prisma : select on many to many with multiple conditions

Question

I have two tables User and Post that are linked by a custom many to many table such as :

model User {
  id            Int      @id @default(autoincrement())
  name          String
  enabled       Bool
  posts         users_to_posts[]
}

model Post {
  id            Int      @id @default(autoincrement())
  name          String
  enabled       Bool
  users         users_to_posts[]
}

model user_to_post {
  user          user? @relation(fields: [user_id], references: [id])
  user_id       Int
  post          post? @relation(fields: [post_id], references: [id])
  post_id       Int
  @@id([user_id, post_id])
}

I am attempting to get a list of users based on a list of post Ids where the users and the posts must be enabled.

So far I can get the right users that are enabled if they have a post that is in the given post array but I cannot check if the post is enabled or not nor can I filter the posts ( I get all of the posts associated with the user if there is a match )

Here is the ( almost ) working code I have :

import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()

export default async function handler(req, res) {
    if (req.method !== 'POST') {
        res.status(400).send({ message: 'Only POST requests allowed for this route' })
    } else {
        const { posts_id } = req.query
        const posts_array = posts_id.split(",").map(function(item) {
            return parseInt(item)
        })
        const ret = await prisma.user.findMany({
            where: {
                enabled: true,
                post: { some: { post_id: { in: posts_array } }, },
            },
            include: {
                _count: { select: { post: true } }
                post: { select: { post: true }, },
            },
        })
        res.status(200).send(ret)
        // ...
    }
}

I am still battling to understand how I can multiple embedded selections without having to rely on typescript to get the query working properly ( witch is far from ideal )

user_8855844 · Answer

From what I understand, there's 2 constraints that you need which isn't represented in your query right now.

Only return a user if the post inside posts_array is enabled.
Filter the returned posts of a user so that it only contains enabled posts.

I've updated your query to add these two conditions.

const users = await prisma.user.findMany({
    where: {
        enabled: true,
        posts: {
            some: {
                post_id: { in: posts_array },
                post: {  
                    enabled: true  // for constraint 1 (only check/match against the post_ids in post array which are enabled)
                }
            },
        },

    },

    include: {
        _count: { select: { posts: true } },
        posts: {
            select: { post: true },
            where: {
                post: {  
                    enabled: true   // for constraint 2 (only include the posts which are enabled)
                }
            }
        },
    },
})

Keep in mind that users[SOME_IDX]._count.posts will contain the count of ALL the posts of that user (including those which are disabled). If you want the count of enabled posts only, you will have to check the length of the users[SOME_IDX].posts array.

By the way, based on your schema, the user_to_post table is somewhat redundant. You could use an implicit many-to-many to model the relation between post and user.

Prisma : select on many to many with multiple conditions

1 Answers