Discuss querying of NULL values

[eikaramba]

Dealing with a relational database feathers-sequelize currently lacks the possibility to query for NULL values.

Especially for relations this is a big bummer, as you might want to only query entities where one or more relations is NULL(basically doing set manipulations like "only give entities from A subset B or B union A etc.") or where a attribute is null.

Currently feathers-rest does not handle this case as null is always translated to String "null". However boolean are converted (i haven't looked at the code).

Anyway my current solution is to use a hook that traverses the query object and converts a null value string back to a null value

handleNullQueries: () => hook => {
    let where = Object.assign({}, hook.params.query);

    function transformQuery(obj) {
      Object.keys(obj).forEach(function (prop) {
        let value = obj[prop];
        if (value !== null && typeof value === 'object')
          obj[prop] = transformQuery(value);
        else if (value === 'NULL') //Yes currently i use uppercase null for this, maybe change it to lowercase
          obj[prop] = null;
      });
      return obj;
    }

    hook.params.query = transformQuery(where);
  }

Any opinion about this? I think there are far more use cases FOR having true null values as for the use case that someone needs to transmit a String 'null'.

[eikaramba]

btw: this is one of my client calls using that:

this.api.service("XXXX").find({$limit:0,query:{a:10,,clickedAt:{$eq:'NULL'}}}).then(....

[daffl]

That is the way to do it. It will work properly already when using Feathers through websockets but it is simply one of the limitations of HTTP that a string URL can not contain any type information. There might be some value in having a query/data schema conversion as a common hook but it is not something the adapter should worry about. It will have to make some very generalized assumptions that will almost certainly not work for some use case.

[eddyystop]

BestBuy has 2 hooks related to this https://github.com/BestBuy/api-playground/blob/master/src/hooks/index.js .

I thought we had already added an issue in hook-common but I now realize we decided not to. In any case, I've added one now feathersjs-ecosystem/feathers-hooks-common#143