Theo's Typesafe Cult•8mo ago

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,
  });

  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: {...

Jump to solution

3 Replies

Solution

ElonMask•8mo ago

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,
            },
          },
        },
      },
    },
  },
})

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

ElonMask•8mo ago

add include and all btw

Davidguy•8mo ago

Yo, this seems to work perfectly. Much appreciated. That actually makes a ton of sense now that i see it

Gaming

Programming

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