I have a unique scenario with two tables, User and Post, that are connected through a custom many-to-many table:
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 currently trying to retrieve a list of users based on a list of post IDs, where both the user and the post must be enabled.
At the moment, I am able to fetch the correct users that are enabled if they have a post matching the provided post array. However, I am facing difficulties in checking if the post is enabled and filtering the associated posts (all posts are retrieved if there is a match).
Here is the code snippet that is almost working:
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 struggling to figure out how to perform multiple embedded selections without relying on TypeScript to correctly execute the query (which is not ideal).