Theo's Typesafe CultTTC
Theo's Typesafe Cult•2y ago
Davidguy

Prisma filtering many-to-many query on exact number of relations

I'm wondering if anyone's aware if it's possible to do a relation query in Prisma where it returns data that has an exact number of relations?

Like for example:
Post 1 has [tag1]
Post 2 has [tag1, tag2]
Post 3 has [tag1, tag2, tag3]

I want to select all posts that have only tag1 and tag2, which is Post 2. My current query has it so that it selects for posts that have at least tag1 and tag2, which would include both Post 2 and Post 3. I'm trying to add more options for stricter filtering on my site.

I can do additional filtering after i get the data back from my database, but that seems kinda jank.

My database is MySQL/PlanetScale

Here's my current query setup:

  const whereClause = {
    AND: tagsToSearch?.map((tag) => ({
      tags: {
        some: {
          name: {
            contains: tag,
          },
        },
      },
    })),
  };

  const posts = await db.post.findMany({
    orderBy: {
      createdAt: sortBy,
    },
    include: {
      user: {
        select: {
          name: true,
        },
      },
      tags: true,
    },
    where: whereClause,
  });


I appreciate any help!
Solution
const REQUIRED_TAGS = ['tag1', 'tag2'];

const posts = await db.post.findMany({
  where: {
    AND: REQUIRED_TAGS.map((tag) => ({
      tags: {
        some: {
          name: tag,
        },
      },
    })),
    NOT: {
      tags: {
        some: {
          NOT: {
            name: {
              in: REQUIRED_TAGS,
            },
          },
        },
      },
    },
  },
})

try this
Jump to solution
Was this page helpful?