Search code examples
mysqlloopbackjs

Correct syntax for IS NULL values with Loopback and MySQL

I use Loopback (v3) to manage endpoints with a MySQL DB. From my app, I use Axios to get end point data.

In the DB, I have this kind of record:

id  name  value   //other columns
1   Sally  0.00
2   Sally  135.00
3   Sally  null

I can query each value in SQL:

select * from Tab where name = 'Sally' and value = 0;
select * from Tab where name = 'Sally' and value is null;
select * from Tab where name = 'Sally' and value > 0;

Problem: I can't replicate some of these queries with Axios from the app (nor with my curl tests). For example, I'd like to get only the null values and can't find the correct syntax for "is null" to put in a Loopback filter.

Here are the curls I tried:

curl -g http://my_ip/api/tabs <-- works as expected

curl -g http://my_ip/api/tabs\?filter\[where\]\[value\]\=null <-- returns only the 0 values and none of the null ones!

So, to spot only the null ones, I tried this solution:

curl -g http://my_ip/api/tabs\?filter\[where\]\[value\]\[eq\]\=null | jq .

It returns an error (and anyway, I can't find a "eq" operator in the list provided by the loopback docs.

How could I get the distinct 0 and null values with loopback, replicating what I got directly in MySQL? Is there a way to make a "is null" filter with Loopback?

Solution

  • Disclaimer: I am a co-author and maintainer of LoopBack.

    Query string parameters have always a string value, it's not possible to tell whether the value null was meant as a string "null" or as JavaScript keyword null.

    Fortunately LoopBack supports also JSON encoding for complex query string arguments. With JSON, it's easy to distinguish between numbers, strings and null values.

    Here is the query to list all model instances with NULL value:

    curl -g 'http://my_ip/api/tabs?filter={"where":{"value":null}}'

    If you are building the request URL in JavaScript, just convert your filter argument from an object into a string via JSON.stringify and let your HTTP client library to URL-encode the value.

    Here is a typical request you will get as a result:

    http://my_ip/api/tabs?filter=%7B%22where%22%3A%7B%22value%22%3A%20null%7D%7D