5 replies

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

Post 1

has

[tag1]

[tag1]

Post 2

Post 2

has

[tag1, tag2]

[tag1, tag2]

Post 3

Post 3

has

[tag1, tag2, tag3]

[tag1, tag2, tag3]

I want to select all posts that have only

tag1

tag1

and

tag2

tag2

, which is

Post 2

Post 2

. My current query has it so that it selects for posts that have at least

tag1

tag1

and

tag2

tag2

, which would include both

Post 2

Post 2

and

Post 3

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

Jump to solution

TTC

Theo's Typesafe Cult•2y ago•

5 replies

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

Post 1

has

[tag1]

[tag1]

Post 2

Post 2

has

[tag1, tag2]

[tag1, tag2]

Post 3

Post 3

has

[tag1, tag2, tag3]

[tag1, tag2, tag3]

I want to select all posts that have only

tag1

tag1

and

tag2

tag2

, which is

Post 2

Post 2

. My current query has it so that it selects for posts that have at least

tag1

tag1

and

tag2

tag2

, which would include both

Post 2

Post 2

and

Post 3

Post 3

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

Jump to solution

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

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

Similar Threads

Similar Threads

Similar Threads