Prisma•7d ago

`isNot: null` filter on relation does not work

As you see in the image i use the not null filter in order to return routes that are assign to both regions. Currently all routes do not have a region. But the console log returns null for the relation. Do I missunterstand the isNot filter or what could be the problem?

4 Replies

RaphaelEtim•7d ago

Hi @schettn What is your expected output becuase If all routes truly do not have a region (meaning the trafficRegion and tourismRegion fields are either null or not set in the database), using the isNot: null filter will not return any results.? This is because the query is looking for routes where these fields are not null, but in your case, they are all null.

schettnOP•7d ago

The issue is, although no routes have associated region, ‘routesData’ contains all routes. I thought that by providing ‘isNot: null’ to the relations, it will exclude all routes that have no region. In my case all of them. So I expect an empty array. The console log shows the relation of the first route which is null, thus this route should not be there. Right?

RaphaelEtim•7d ago

Can you try this query?

const routesData = await client.route.findMany({
  where: {
    gtfsimportid,
    NOT: {
      OR: [
        { trafficRegion: null },
        { tourismRegion: null }
      ]
    }
  }
})

const routesData = await client.route.findMany({
  where: {
    gtfsimportid,
    NOT: {
      OR: [
        { trafficRegion: null },
        { tourismRegion: null }
      ]
    }
  }
})

The console log shows the relation of the first route which is null, thus this route should not be there. Right?

You're correct that if all routes have no associated region (i.e., the relations are null), using isNot: null should exclude all of them, resulting in an empty array.

RaphaelEtim•7d ago

I found an issue with the isNot filter not working correctly

GitHub

Generated RelationFilter not allowing null values after seemingly u...

Bug description I hit an issue where performing a query like prisma.group.findMany({where: {activeGroup: {isNot: null}}}) would fail, only after adding some seemingly unrelated relations. It seems ...

Gaming

Programming

`isNot: null` filter on relation does not work