CODEWITHSUNDEEP
sqlnestjstypeorm
Zulfe
Zulfe

Reputation: 881

Excluding undefined Field Values in MariaDB TypeORM Queries

I have a basic Nest project using TypeORM. A model, Plate, has been contained in its own module and is imported into the main module.

The service for the Plate model contains the following method:

public query(params?: QueryPlateParams, limit: number = 100): Promise<Plate[]> {

    const findOp = PlatesService.FindOperatorMap[params.type];

    const whereObj = {
        // If a find operation is defined, apply it to the value. Otherwise, just use the
        // value itself.
        id: findOp?.(params.value) ?? params.value,
        available: params.isAvailable,
        state: params.state
    };
    console.log(whereObj);

    return this._platesRepository.find({
        where: whereObj,
        take: limit
    });
}

The idea of this method is to take the query object, which could have any combination of the value, isAvailable, and state properties defined, then to apply the defined values to the query's WHERE clause with some modification, of course not defining filters for fields which had no value in the source query object.

When a query is made, the following logs are observed.

{ id: 'ABSOLVE', available: undefined, state: undefined }
query: SELECT `Plate`.`id` AS `Plate_id`, `Plate`.`state` AS `Plate_state`, `Plate`.`available` AS `Plate_available`, `Plate`.`lastChecked` AS `Plate_lastChecked` FROM `plates` `Plate` WHERE (`Plate`.`id` = ? AND `Plate`.`available` = ? AND `Plate`.`state` = ?) LIMIT 100 -- PARAMETERS: ["ABSOLVE",null,null]

What's important here is the parameter interpolation at the end of the second line: PARAMETERS: ["ABSOLVE",null,null]. The values for isAvailable and state are undefined, but the SQL statement translated them to null, but undefined and null are distinct values in Javascript/Typescript. The intent, of course, is to ignore undefined values in the query, not interpret them as null.

I found an option for the MongoDB connector that will ignore these undefined values. I do not see the same for the MariaDB/MySQL connector, which is what is being used for this service.

Is there any option to ignore these undefined values instead of interpreting them as null? I'd like to use the IsNull() or null values to explicitly check for SQL NULL.

Upvotes: 0

Views: 2532

Answers (1)

Seti
Seti

Reputation: 2314

Sadly you need just to not provide the undefined fields (add filter before passing whereObj into the find function.

Or you can read this thread with some other options dealing with the issue https://github.com/typeorm/typeorm/issues/2934

Basically - the issue is because creator thought that setting property of object to undefined removes the property from the object own properties therefore he treated undefined and null the same.

Upvotes: 3

Related Questions