Optimisation: Combine `where` directives on related records

[Sytten]

Problem

It is easy for users to mess up and created really bad queries when using filters on related fields.

Consider:

if (filter?.startDate) {
    conditions.push({
      refunds: { some: { createdAt: { gte: filter.startDate } } },
    });
  }
  if (filter?.endDate) {
    conditions.push({
      refunds: { some: { createdAt: { lte: filter.endDate } } },
    });
  }

Here we have a relation between ticket and refund. This will generated something like:

SELECT
        "public"."ticket"."id"
    FROM
        "public"."ticket"
    WHERE ("public"."ticket"."type" = 'SELL'
        AND "public"."ticket"."status" = 'REFUNDED'
        AND("public"."ticket"."id")
        IN(
            SELECT
                "t0"."id" FROM "public"."ticket" AS "t0"
                INNER JOIN "public"."ticket_refund" AS "j0" ON ("j0"."ticket_id") = ("t0"."id")
            WHERE ("j0"."created_at" >= '2022-04-01'::TIMESTAMP
                AND "t0"."id" IS NOT NULL))
        AND("public"."ticket"."id")
        IN(
            SELECT
                "t0"."id" FROM "public"."ticket" AS "t0"
                INNER JOIN "public"."ticket_refund" AS "j0" ON ("j0"."ticket_id") = ("t0"."id")
            WHERE ("j0"."created_at" <= '2022-09-01'::TIMESTAMP
                AND "t0"."id" IS NOT NULL)))

Suggested solution

Prisma should be able to rewrite conditions to avoid unnecessary joins.

Alternatives

Do:

if (filter?.startDate || filter?.endDate) {
    conditions.push({
      refunds: {
        some: {
          createdAt: {
            gte: toUndefined(filter.startDate),
            lte: toUndefined(filter.endDate),
          },
        },
      },
    });

[luxaritas]

@Weakky Looking at the linked PR description, it looks like this was accidentally closed when it shouldn't have been? Or did that caveat change?

[janpio]

Ha, GitHub reads "not fix #" as "fix #" 🤷 Sorry.