Theo's Typesafe Cult•2y ago

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•2y 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 },
             ] 
             },
          ]
      },
    });
  }),

deforestorOP•2y 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•2y ago

you could also use nullish coalescing with the ??

deforestorOP•2y 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•2y ago

yess exactly pretty cool right?

deforestorOP•2y ago

Yes, will do it later, thank you!

erik.gh•2y ago

Gaming

Programming

Confused on Prisma's where clause: OR and AND