$null filter in relation not working

[daniel-maegerli]

Hi, I've been fiddling around with an issue for a while now, which looks like a bug to me. I want to filter on a relation's property "viewedAt" (which is datetime or NULL in the database) but cannot make it entirely work. Configuration (excerpt):

const paginatedProfiles = await paginate(query, profiles, {
  relations: ['matches'],
  filterableColumns: {
    'matches.viewedAt': [FilterOperator.NULL],
  },
});

When I do a request with &filter.matches.viewedAt=$null everything gets delivered, no matter if the "viewedAt" is NULL or not.
When I do a request with &filter.matches.viewedAt=$not:$null it actually delivers to correct items, having "viewedAt" set (although I haven't even set the FilterSuffix.NOT in the configuration).

Payload 1 (excerpt):

{
  "meta": {
    "itemsPerPage": 10,
    "totalItems": 2,
    "currentPage": 1,
    "totalPages": 1,
    "filter": {
      "matches.viewedAt": "$null"
    }
  },
  "links": {
    "current": "http://localhost:8400/profiles/9f5fdc67-6541-45b1-ae61-8f6b25627a11?page=1&limit=10&filter.matches.viewedAt=$null"
  },
  "data": [
    {
      "match": {
        "matchingScore": 48.3,
        "viewedAt": "2023-03-15T09:15:34.000Z"
      }
    },
    {
      "match": {
        "matchingScore": 34.2,
        "viewedAt": null
      }
    }
  ]
}

Payload 2 (excerpt):

{
  "meta": {
    "itemsPerPage": 10,
    "totalItems": 1,
    "currentPage": 1,
    "totalPages": 1,
    "filter": {
      "matches.viewedAt": "$not:$null"
    }
  },
  "links": {
    "current": "http://localhost:8400/profiles/9f5fdc67-6541-45b1-ae61-8f6b25627a11?page=1&limit=10&filter.matches.viewedAt=$not:$null"
  },
  "data": [
    {
      "match": {
        "matchingScore": 48.3,
        "viewedAt": "2023-03-15T09:15:34.000Z"
      }
    }
  ]
}

Any idea? I've tried different versions and am on latest release (5.0.4) now. Still no luck to make it work.

[ppetzold]

Hmm, unfortunately we don't have coverage for $null in a one-to-many relation. Only those relate:

https://github.com/ppetzold/nestjs-paginate/blob/master/src/paginate.spec.ts#L697
https://github.com/ppetzold/nestjs-paginate/blob/master/src/paginate.spec.ts#L1512

Could you submit a PR with 2 test cases covering your examples?

[daniel-maegerli]

Let me know if this is what you expected, here's the PR: #539

[ppetzold]

is this still an issue with v6+ ?

[Helveg]

Yes, the issue persists. The problem arises from left joining the home relationship, and then filtering on home.street IS NULL. Records that have home IS NULL somehow pass the qb.andWhere('__root_home_rel.street IS NULL') and are then included because of the left join on __root_home_rel.

A workaround for now is to include where: { home: Not(IsNull()) } in your pagination config.

I think the best way to fix this is for the addWhereCondition in filter.ts to include IS NOT NULL clauses for all the parent relationships it depends on.