Prisma•2y ago

Prisma ORM returns two different results from two similar (?) queries

Hi everyone !

Could someone help me figure out why this two queries don't return the same result ? As I understand, the values given to

is_deleted

is_deleted

type

type

and

inserted

inserted

should be mandatory for both queries, but it seems that only the second one, with the duplicated common fields in both

OR

OR

'branches', give me the correct result.

// Returns only one row
await this.prisma.alert.findMany({
      where: {
        is_deleted: withDeleted ? undefined : false,
        type: alertType === 'all' ? undefined : alertType,
        inserted: {
          gte: startDate,
          lt: endDate,
        },
        OR: [
          {
            group_id: {
              in: groupIds,
            },
          },
          {
            groups: {
              some: {
                group_id: {
                  in: groupIds,
                },
              },
            },
          },
        ],
      }
});

// Returns 10 rows as intended
await this.prisma.alert.findMany({
      where: {
        OR: [
          {
            is_deleted: withDeleted ? undefined : false,
            type: alertType === 'all' ? undefined : alertType,
            inserted: {
                gte: startDate,
                lt: endDate,
            },
            group_id: {
              in: groupIds,
            },
          },
          {
            is_deleted: withDeleted ? undefined : false,
            type: alertType === 'all' ? undefined : alertType,
            inserted: {
                gte: startDate,
                lt: endDate,
            },
            groups: {
              some: {
                group_id: {
                  in: groupIds,
                },
              },
            },
          },
        ],
      }
});

// Returns only one row
await this.prisma.alert.findMany({
      where: {
        is_deleted: withDeleted ? undefined : false,
        type: alertType === 'all' ? undefined : alertType,
        inserted: {
          gte: startDate,
          lt: endDate,
        },
        OR: [
          {
            group_id: {
              in: groupIds,
            },
          },
          {
            groups: {
              some: {
                group_id: {
                  in: groupIds,
                },
              },
            },
          },
        ],
      }
});

// Returns 10 rows as intended
await this.prisma.alert.findMany({
      where: {
        OR: [
          {
            is_deleted: withDeleted ? undefined : false,
            type: alertType === 'all' ? undefined : alertType,
            inserted: {
                gte: startDate,
                lt: endDate,
            },
            group_id: {
              in: groupIds,
            },
          },
          {
            is_deleted: withDeleted ? undefined : false,
            type: alertType === 'all' ? undefined : alertType,
            inserted: {
                gte: startDate,
                lt: endDate,
            },
            groups: {
              some: {
                group_id: {
                  in: groupIds,
                },
              },
            },
          },
        ],
      }
});

MoymatOP•5/30/24, 6:18 PM

The goal of the query is to find all s which, for example, have , and were instered between and . They also either have to have a one-to-many relation with a which id is in (ex: ) ( or a many-to-many relation with ().
Also, could be

undefined

undefined

, which should ignore the

OR

OR

clause if I'm not mistaken.

MoymatOP•5/30/24, 6:30 PM

After writing that, I noticed that the issue only appears for the first query if is undefined, not if I give it an array of ids.

MoymatOP•5/30/24, 6:38 PM

I figured it out. In the first query, if

OR

OR

doesn't have any filter (if is

undefined

undefined

), it returns an empty list. That's not the case for the second one since the other fields are present.