TTC
Theo's Typesafe Cult•11mo 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,
  });
  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
ElonMask•11mo 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
ElonMask•11mo ago
add include and all btw
Davidguy
DavidguyOP•11mo ago
Yo, this seems to work perfectly. Much appreciated. That actually makes a ton of sense now that i see it

Did you find this page helpful?