Confused on Prisma's where clause: OR and AND

I'm trying to write a query that queries the repositories that aren't hidden for everyone, but for the logged user, it also queries the hidden ones. I think the SQL for that would be something like: SELECT * FROM Repository r WHERE r.hidden = false OR (r.hidden = true AND r.userId = ctx.session.user.id) which is very simple, but for some reason, I can't get this to work on my procedure at all:
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
hidden: false,
OR: {
hidden: false,
AND: { hidden: true, userId: ctx.session?.user.id },
},
},
});
}),
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
hidden: false,
OR: {
hidden: false,
AND: { hidden: true, userId: ctx.session?.user.id },
},
},
});
}),
7 Replies
royanger
royanger2y ago
Been working with TypeORM so memory might be fuzzy, gut I think you want something like the following: ` getAllBumpedRepos: publicProcedure.query(({ ctx }) => { return ctx.prisma.repository.findMany({ include: { techs: true }, where: { OR: [ { hidden: false }, { AND: [ { hidden: true }, { userId: ctx.session?.user.id }, ] }, ] }, }); }),
deforestor
deforestor2y ago
That's still bringing the "hidden" ones to the non authenticated user Huh.. that's so strange. I suspected maybe this is because ctx.session?.user.id is undefined when not authenticated, and doing this worked:
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
const id = ctx.session?.user.id || "";
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
OR: [{ hidden: false }, { AND: [{ hidden: true }, { userId: id }] }],
},
});
}),
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
const id = ctx.session?.user.id || "";
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
OR: [{ hidden: false }, { AND: [{ hidden: true }, { userId: id }] }],
},
});
}),
shouldn't {userId: undefined} be false?
erik.gh
erik.gh2y ago
you could also use nullish coalescing with the ??
deforestor
deforestor2y ago
so it would be, like:
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
OR: [{ hidden: false }, { AND: [{ hidden: true }, { userId: ctx.session?.user.id?? ""}] }],
},
});
}),
getAllBumpedRepos: publicProcedure.query(({ ctx }) => {
return ctx.prisma.repository.findMany({
include: { techs: true },
where: {
OR: [{ hidden: false }, { AND: [{ hidden: true }, { userId: ctx.session?.user.id?? ""}] }],
},
});
}),
like that? true, I always forget this exists lmao
erik.gh
erik.gh2y ago
yess exactly pretty cool right?
deforestor
deforestor2y ago
Yes, will do it later, thank you!
erik.gh
erik.gh2y ago
np
Want results from more Discord servers?
Add your server