Filter by 'null' value is not taken in consideration

[izziaraffaele]

Dataprovider queries do not take into consideration a filter with null value which is stripped and excluded from the final query sent to the server.

[sMteX]

Until it's implemented, you can somewhat workaround it by overriding the data provider and modifying the filter object (e.g. I had a situation where effectiveTo is often null but still wanted to use it if it's defined):

const dataProvider = useDataProvider(...)

const customDataProvider: LegacyDataProvider = (type: string, resource: string, _params: any) => {
    // clone params so we don't mutate input
    const params = { ..._params }
    // custom filter - field "effectiveInRange" doesn't exist
    if (type === GET_LIST && resource === 'Entity' && params.filter.effectiveInRange) {
      const targetDate: string = params.filter.effectiveInRange
      delete params.filter.effectiveInRange

      params.filter = {
        ...params.filter,
        OR: [
          // situations where effectiveTo is defined
          {
            effectiveFrom_lte: targetDate,
            effectiveTo_gte: targetDate,
          }, 
          // when effectiveTo is null
          {
            effectiveFrom_lte: targetDate,
            // we have to specify the equals, `effectiveTo: null` converts it to new Date(null) = 1.1.1970
            effectiveTo: {
              equals: null
            }
          }
        ]
      }
    }

    return dataProvider(type, resource, params)
}

[macrozone]

can you check again with newest version? i just merged a PR https://github.com/panter/ra-data-prisma/pull/60 but i am not sure if it solves your problem

[sMteX]

The PR probably fixed only where it's in the NOT, OR or AND operators. In my case, effectiveTo: null still gets into https://github.com/panter/ra-data-prisma/blob/master/packages/dataprovider/src/buildWhere.ts#L109 and outputs

effectiveTo: {
  equals: "1970-01-01T00:00:00.000Z"
}

[macrozone]

@sMteX would be awesome if you could send a PR that fixes it!

[sMteX]

Once I have a bit more time I could potentially take a look at that, until then

field: {
  equals: null
}

fortunately works

[macrozone]

https://github.com/panter/ra-data-prisma/blob/master/packages/dataprovider/README.md#custom-filters should also work around this!