Querying by empty multiple-choice-checklist

General Q&A
1

I am trying to query some objects in my database by a multiple-choice-checklist field. The goal is to retrieve all objects where the list is null/empty OR includes a particular value X. My thought was to use:

view.objects = DB.object.where('list contains ? or list = ?', 'X', null)

This works except when someone has first selected a value for the object and then removed that value. The result being that the list is empty but not null. I then tried:

view.objects = DB.object.where('list contains ? or list = ? or list = ?', 'X', null, [])

But this throws an error (I replaced some names in my above examples for clarity):

image

Suggestions?

2

As a workaround I added an on-change function to the field under object:

function setNullIfEmpty() {
    if (!view.object.list || view.object.list.length == 0) {
        view.object.list = null;
    }
}

This way if all of the options are removed, the code will set it to null so it works with my above query. Seems to work well, but if someone has a way to fix it on the query side that would be good to see too

3

I’m curious if you used undefined rather that [] in your query if it would work?

view.objects = DB.object.where('list contains ? or list = ? or list = ?', 'X', null, undefined)

it seems to not like the [] character. But, i would assume the value if you tried to log it would be undefined.

4

@fthomas Try querying by "[]".

So in your case: view.objects = DB.object.where('list contains ? or list = ? or list = ?', 'X', null, "[]")