Optional where relation query?

[KiriLucas]

Hey! So I've got this:

await this.prisma.modules.findMany({
  where: {
    AND: [
      {
        name: {
          in: filters.names,
        },
        tags: {
          some: {
            AND: [
              {
                tagTypes: {
                  name: filters.tag1,
                },
                value: filters.tag1,
              },
              {
                tagTypes: {
                  name: filters.tag2,
                },
                value: filters.tag2,
              },
            ],
          },
        },
      },
    ],
  },
});

I have two records that match the filters.names filter (let's say I've got two records with the name 'Potato'), but since tag1 and tag2 might be undefined (they're optional query params), whenever I send a names but no tag1 or tag2, I've got a single result.
Debugging my code, I found out that it's because only one of those records has a relation with the tags table, so, by doing
tags: { some: // ........ } I mess my query up, and instead of fetching 2 records (with name 'Potato') I only fetch that with a relation to tags table... Since that relation is optional, something like this is necessary:

tags: (filters.tag1 || filters.tag2) ? { some: // ........ } : undefined
That way, I only build my query if at least one filter is present, but that would be weird if I had 20+ filters, even if I used a function only for that. So, my question is, on my case, is there any way to make things a little bit cleaner?

// Edit: Forgot to mention that my filters are meant to narrow down results, so I must use an AND statement, as OR would widen up my query results.

[ludralph]

Hi @KiriLucas 👋

You can dynamically build the where object based on the presence of filters. For example:

const where = {
  name: {
    in: filters.names,
  },
};

if (filters.tag1 || filters.tag2) {
  where.tags = {
    some: {
      AND: [],
    },
  };

  if (filters.tag1) {
    where.tags.some.AND.push({
      tagTypes: {
        name: filters.tag1,
      },
      value: filters.tag1,
    });
  }

  if (filters.tag2) {
    where.tags.some.AND.push({
      tagTypes: {
        name: filters.tag2,
      },
      value: filters.tag2,
    });
  }
}

await this.prisma.modules.findMany({
  where: {
    AND: [where],
  },
});

we first define the base where object with the name filter. If either filters.tag1 or filters.tag2 is present, we add the tags filter to the where object. We create an empty AND array within the tags.some filter and push the individual tag filters if they are present. Finally, we pass the where object to the findMany method.

[KiriLucas]

Hey! As I mentioned on my post, that is EXACTLY what I want to avoid doing, I'm not even sure there's another cleaner way of doing it, as that might look ok for two filters, but that is not sustainable if I have 20 filters.

[ludralph]

Hi @KiriLucas, In that case, you can leverage dynamic object composition techniques to create the where object based on the available filters. for example:

const filters = {
  names: ['Potato'],
  tag1: 'tag1Value',
  tag2: undefined,
  // ... other filters
};

const buildTagFilter = (tagName, tagValue) => ({
  tagTypes: {
    name: tagName,
  },
  value: tagValue,
});

const where = {
  name: {
    in: filters.names,
  },
  tags: {
    some: {
      AND: Object.entries(filters)
        .filter(([key, value]) => key.startsWith('tag') && value !== undefined)
        .map(([key, value]) => buildTagFilter(key, value)),
    },
  },
};

await this.prisma.modules.findMany({
  where: {
    AND: [where],
  },
});

We define a buildTagFilter function that takes the tag name and value and returns the corresponding filter object. We then create the where object, including the name filter as before. The tags.some.AND array is constructed dynamically by iterating over the filters object and filtering out the keys that start with 'tag' and have a non-undefined value. For each valid filter, we call the buildTagFilter function to create the filter object. This approach allows you to handle multiple filters in a more automated and scalable manner. You can add or remove filters in the filters object, and the code will dynamically generate the appropriate where object based on the available filters.

[ludralph]

Hi @KiriLucas 👋

Do you have any more questions around this issue? If no, it would be really helpful if you could quickly mark this Discussion as answered to indicate that your issue has been resolved.

In case you do have any outstanding questions, feel free to let us know and we'd be happy to help!

[nurul3101]

We are closing this Discussion due to inactivity. Please open a new Discussion if you have a similar problem 🙏

[gesfon]

Thanks a lot! Object.assign() instead of push() that's what I needed!