fix: allow where IsNull for ManyToOne relations

[pixtron]

Fixes: #8890

Description of change

Allows to query ManyToOne relations for records that do not have any relation (IS NULL).

Pull-Request Checklist

• Code is up-to-date with the master branch
• npm run format to apply prettier formatting
• npm run test passes with this change
• This pull request links relevant issues as Fixes #0000
• There are new or updated unit tests validating the change
• Documentation has been updated to reflect this change
• The new commits follow conventions explained in CONTRIBUTING.md

[jczacharia]

This doesn't appear to work with OR operations:
E.g.:

    return this.caseRepo.find({
      where: [
        {
          snf: { id: In(queriedIds) },
        },
        {
          snf: IsNull(),
        },
      ],
    });

It appears because the query is doing an INNER JOIN and not a LEFT JOIN for the IN operator.

[pixtron]

@jczacharia this workaround might work for you.

[pleerock]

Thank you for contribution! 🎉

[LoneRifle]

Hello @pixtron @pleerock - this may have introduced a regression where selecting for OneToMany relations using moreThanOrEqual or lessThanOrEqual operators are now disallowed. Is this intended, and if not, should a PR be submitted so that for relation.isOneToMany we do not throw the error?

[pixtron]

@LoneRifle i just removed the throw you are mentioning locally, it wouldn't solve your issue.
But you are pretty close. moreThanOrEqual and lessThanOrEqual should probably be implemented here for relation.isOneToMany, relation.isManyToManyOwner and relation.isManyToManyNotOwner.

I had a quick test with this change, which made moreThanOrEqual and lessThanOrEqual working for isOneToMany and probably will for the other relations too.

diff --git a/src/query-builder/SelectQueryBuilder.ts b/src/query-builder/SelectQueryBuilder.ts
index d3b01053f..11b5b0ea4 100644
--- a/src/query-builder/SelectQueryBuilder.ts
+++ b/src/query-builder/SelectQueryBuilder.ts
@@ -4321,12 +4321,16 @@ export class SelectQueryBuilder<Entity extends ObjectLiteral>
                     }

                     if (InstanceChecker.isFindOperator(where[key])) {
+                        const { type } = where[key];
                         if (
-                            where[key].type === "moreThan" ||
-                            where[key].type === "lessThan"
+                            type === "moreThan" ||
+                            type === "moreThanOrEqual" ||
+                            type === "lessThan" ||
+                            type === "lessThanOrEqual"
                         ) {
-                            const sqlOperator =
-                                where[key].type === "moreThan" ? ">" : "<"
+                            const baseOperator = /^moreThan/.test(type) ? ">" : "<"
+                            const sqlOperator = baseOperator + (/OrEqual$/.test(type) ? '=' : '')
+
                             // basically relation count functionality
                             const qb: QueryBuilder<any> = this.subQuery()
                             if (relation.isManyToManyOwner) {

Probably the best is if you open a new issue, and create a PR for that issue. The PR probably has the best chance to get merged if you thoroughly test moreThanOrEqual and lessThanOrEqual for isOneToMany, isManyToManyOwner and isManyToManyNotOwner relations.

Also one could argue that the Find operators Equal, Between (and maybe some more?) should be supported as well for relations. But thats eventually yet another issue and PR.

[LoneRifle]

@pixtron - I got it to work, not by removing the throw, but not throw the exception by changing the else to else if (!relation.isOneToMany) (edit: but as you point out, it did not work as intended). Also corroborated this by testing on 0.3.7 (working) and 0.3.8 (not working, contains this change), which was how I discovered this PR.

My condition change felt pretty hacky though, so I did a more invasive change similar to yours (with String.startsWith and String.endsWith instead of regexs) and verified that this other approach works just as well.

I'll see if I can find the time to submit a PR. Submitted #9978. While MoreThanOrEqual on relations matters to us, we could possibly workaround with the equivalent MoreThan expression.