sql - typeorm: how to properly use IsNotNull/IsNull?

Question

We created a helper function to create wheres easier. It works fine with eq, neq, lt and gt. Now we're trying to add is null/is not null (for a date column, not sure if that matters).

The critical part of the function looks like this:

// This is ran in a loop for every attribute
const query = `${attribute}` ${comparator} :value${index}`;

// if the checked 'value' is NULL then use IsNull(), same for NOT NULL, otherwise simply use value 
const params = { [`value${index}`]: value == 'NULL' ? IsNull() : value === 'NOT NULL' ? Not(IsNull()) : value};

// Add this (sub)query to the qb
qb.andWhere(query, params);

Now we get an error saying this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’_type = ‘not’, _value = ‘[object Object]‘, _useParameter = true, `_multipl’ at line 1"

Value is [object Object] - which kind of makes sense if we use IsNotNull(), right?

As far as I understand from this comment, IsNull() and Not(IsNull()) should work like we are trying to. We use @nestjs/typeorm 7.1.5.

question from:https://stackoverflow.com/questions/66045886/typeorm-how-to-properly-use-isnotnull-isnull

Answer 1

To check for NULL you need

qb.andWhere(`${attribute} IS NULL`)

To check for NOT NULL you need

qb.andWhere(`${attribute} IS NOT NULL`)

(Note: Omit the second argument, parameters, for these cases).

From your code seems you are using string values 'NULL' and 'NOT NULL' as the value arguments and checking these as special cases. Your code will now look like this:

if ((value == 'NULL' && comparator == '=') || (value == 'NOT NULL' && comparator == '<>'))
    qb.andWhere(`${attribute} IS NULL`);
if ((value == 'NOT NULL' && comparator == '=') || (value == 'NULL' && comparator == '<>'))
    qb.andWhere(`${attribute} IS NOT NULL`);
else
    qb.andWhere(`${attribute} ${comparator} :value${index}`, { [`value${index}`]: value});

(In the code above I check for '=' and '<>' which are standard SQL comparison operators. If your SQL dialect uses 'eq' and 'ne' in place of '=' and '<>', which you mention in your question, you will need to change the code above. If so please update your question and add the appropriate tag to say which SQL database you are using).

When you test this, I recommend that you turn on TypeOrm full logging so you can see the actual generated SQL and you be able to quickly solve any problems. See TypeOrm logging.