Prisma•6mo 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, type and inserted should be mandatory for both queries, but it seems that only the second one, with the duplicated common fields in both 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,
                },
              },
            },
          },
        ],
      }
});

1 Reply

MoymatOP•6mo ago

The goal of the query is to find all alerts which, for example, have is_deleted = false, type = 'urgent' and were instered between 2024-05-01 and 2024-05-30. They also either have to have a one-to-many relation with a group which id is in groupIds (ex: [1, 2, 3]) (group_id: { in: [1, 2, 3] }) or a many-to-many relation with groups (groups : { some : { group_id : { in : [1, 2, 3] } } }). Also, groupIds could be undefined, which should ignore the OR clause if I'm not mistaken. After writing that, I noticed that the issue only appears for the first query if groupIds is undefined, not if I give it an array of ids. I figured it out. In the first query, if OR doesn't have any filter (if groupIds is undefined), it returns an empty list. That's not the case for the second one since the other fields are present.

Gaming

Programming

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