Operators have undefined effects on toMany relationships

[Helveg]

While working more on #1064 and related issues, I noticed that the current nestjs-paginate semantics don’t correctly handle to-many relationship filters.

I think it’s best illustrated by these cases:

• filter.toys.id=$null: include only cats without toys. Currently works, though with some issues — see Using $null on non-terminal relationship returns empty results #1064

• filter.toys.id=$not:$null: should include cats with toys. Not sure if it currently works; same issue as above.

• filter.toys.color=$eq:red: should include cats that have a red toy. Currently returns only the red toys of those cats, not all their toys — see Filtering on relations trims relation arrays instead of returning all related entities #1105

• filter.friends.id=$not:$eq:3: should exclude cats that are friends with cat 3. Currently includes cats that are friends with both 3 and others.

The root cause is that filters on related columns use WHERE against joined tables, which filters the related rows rather than the parent entity. To behave correctly, these should instead be built using EXISTS / NOT EXISTS conditions that determine inclusion at the parent level.

Proposed plan:

• Keep $not and $null semantics unchanged for direct columns.

• Switch relationship filters from WHERE to EXISTS logic.

• Define clear semantics for $not, $none, and $null on relationships:

Operator	Description	Example
$op	Include entities where one or more related entities match the operation	Find cats with red toys
$not:$op	Include entities where one or more related entities match the negated operation (excluding those without related entities)	Find cats with toys that are not red
$none:($not:)$op	Include entities where zero related entities match the operation (including those without related entities)	Find cats without (non-)red toys
$null	Include entities with zero related entities	Find cats without toys
$not:$null	Include entities with one or more related entities	Find cats with toys

All these should filter only the parent entity, leaving the returned joined relationships untouched. I also propose that we apply `$null` to the relationship rather than the primary column, since that doesn't work well with compound primary columns.