$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.

[guesant]

hello guys, after an investigation, i found a solution that may work

cc @ppetzold @Helveg

did someone noticed that because $not is not inside allowedOperations, the parseFilter never returns $not:$null?

nestjs-paginate/src/filter.ts

Line 350 in df69631

const filter = parseFilter(query, filterableColumns, qb)

Expected

array that includes the $not and $null combined find operator

Actual

empty array...

by the way i'm using nestjs-paginate at version 8.6.3

the line that ignores the $not is the following:

nestjs-paginate/src/filter.ts

Lines 277 to 279 in df69631

	if (token.suffix && !allowedOperators.includes(token.suffix)) {
	continue
	}

without that continue when suffix == "$not", it cames to here

so...

when adding a check if to not ignore the $not suffix

it does work properly

db rows

[guesant]

tldr:

nestjs-paginate/src/filter.ts

Lines 277 to 279 in df69631

	if (token.suffix && !allowedOperators.includes(token.suffix)) {
	continue
	}

- if (token.suffix && !allowedOperators.includes(token.suffix)) {
+ if (token.suffix && !allowedOperators.includes(token.suffix) && token.suffix !== "$not") {

[guesant]

@daniel-maegerli, try this workaround:

• add literal "$not" into colum's filter array:

filterableColumns: {
  "diarios.id": [FilterOperator.EQ, FilterOperator.NULL, "$not"],
},

é vapo paizão

[guesant]

FilterSuffix.NOT make this works...

this still an issue?

if not, i think it should be better documentend.

[guesant]

thats already on the docs, I didn't noticed