need ability to query for null values (or filter for non-null values) in collections

[basatS7T]

When creating collections, some attributes are bound to be optional (not all attributes can be required). By neglect or error, null values will thus find their way into collections via optional attributes. The only "sure way" to avoid this is to define default values for all optional attributes. BUT, sooner or later, Murphy will enter the picture and null values will appear! Either a line of code will forget an optional attribute or a backup/restore procedure will or some legacy collection already has null value(s)! After all, like me, some users may not have been aware of the fact that null values are out of bounds for queries -- some may still be unaware. They may (still) be operating under the assumption null values can be queried/filtered (and filled) programmatically. When they discover the truth, it may be too late! So, please consider providing a way to query for null values in the next PA release of AW.
Thanks!
TBT

Appwrite Version: 0.12.1.201

[pspierce]

Anything further on this one? If I'm not mistaken, as late as 14.2, if you add a boolean attribute to a collection without setting default, then the value of existing documents already in the collection is null for that attribute. I tried applying [attr].notEqual(true), and that didn't appear to do it.

[OHeroJ]

+1

[noob8boi]

+1

[ddenev]

If you will not be providing the ability to query by equality to null then at least allow setting empty string as a default value for attributes (currently when no default is provided, null is assumed by Appwrite).

[stnguyen90]

If you will not be providing the ability to query by equality to null then at least allow setting empty string as a default value for attributes (currently when no default is provided, null is assumed by Appwrite).

@ddenev, it makes sense to be able to have an empty string be a default. Since it seems like this might be an unrelated bug, would you be able to create a new issue for it?

[ddenev]

@stnguyen90, I'm not sure this is a bug. Here is why:

1. When creating a string attribute with empty default value, the DB table column is created as:
   'next_id' VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', which means it can have NULL as a value and the default is NULL in the DB.
2. The _1_attributes table of the appwrite database contains this row:
   
3. The _1_metadata table of the appwrite database contains the following JSON for the collection (notice the 'default' value):
   {"$id":"next_id","key":"next_id","type":"string","size":255,"required":false,"default":null,"signed":true,"array":false,"format":"","formatOptions":[],"filters":[]}

So it seems rather deliberate and also I believe it's OK to work that way, i.e. to have null as a default value when no value is provided.

We just need the additional option to be able to specify the empty string as default. Currently you have only a single text input field and when it's empty, you assume null. Add 2 radio buttons:

1. NULL
2. Text

and when the user selects the 'Text' radio then allow entering text in a text input field (in this case when empty, assume the empty string)

That's it - simple :)

[stnguyen90]

@ddenev, I tested by creating an attribute with default as "" rather than null. As such, I would expect the default value to be "", but they ended up being null instead. There may be a bug with how we're creating the attribute and inserting that default value into the _metadata table.

[ddenev]

@stnguyen90, sorry, could you clarify?

Are you inputting "" (two double-quotes) in the input field or are you NOT providing any value at all?

[stnguyen90]

@stnguyen90, sorry, could you clarify?

Are you inputting "" (two double-quotes) in the input field or are you NOT providing any value at all?

@ddenev, not putting anything at all. When inspecting the network request, you'll see that an empty string is sent for the default parameter.

[ddenev]

Dear @eldadfux

I consider null values in the DB to be valid carriers of business information - e.g. I use null values to explicitly express "no value".

Therefore I have a number of documents with null attributes which I need to query. The lack of Query.isNull() makes me use hacky methods (e.g. list all and do it in the code) which I would like to avoid (e.g. imagine I have a collection with thousands of documents).

Therefore, do you plan to add this to Appwrite?

Thank you!

[BenMemescape]

Dear @eldadfux

I consider null values in the DB to be valid carriers of business information - e.g. I use null values to explicitly express "no value".

Therefore I have a number of documents with null attributes which I need to query. The lack of Query.isNull() makes me use hacky methods (e.g. list all and do it in the code) which I would like to avoid (e.g. imagine I have a collection with thousands of documents).

Therefore, do you plan to add this to Appwrite?

Thank you!

I'd like to add my voice to this. Query.equal('attributeName', null) does not seem to work so we are having to resort to horrible kludges.

NULL is different than 0 or an empty string, we need to be able to query for it. To my mind, making it explicit with something like Query.isNull('attributeName') and Query.isNotNull('attributeName') makes more sense than relying on Query.equal(). The designers of databases have been here before, there are sound reasons why SQL uses IS [NOT] NULL rather than = NULL.

[jbarba4]

+1

[andrewmoore-nz]

I'd also like to add my voice to this, as it's becoming a major headache for me. Due to the way that I have to deal with indexes, this issue means that any attribute that is (and should be/needs to be) empty, or an empty string, gets excluded from a query.

Even if I go with workarounds for default placeholder values e.g. defaulting to a value of "-" or something similar, this use case allows the user to edit the field (e.g. a text attribute of notes, that could easily be empty), so it could easily be changed back to an empty string. I'd really rather avoid having to check every field entry for an empty string and replace it on the client side just so that documents can continue to be correctly queried.

Is this going to be given any priority? It's becoming a serious usability issue the further I go in developing these advanced query structures.

[hugebug4ever]

+1

[eldadfux]

This feature will be added in the next release.

[Edijae]

@eldadfux let us know when this feature has been released

[HasanAboShally]

When I do Query.equal('field', null) I still get the following error:

AppwriteException [Error]: Invalid query: Query type does not match expected: string.
message: 'Invalid query: Query type does not match expected: string',

[troykelly]

@HasanAboShally Wouldn't you need to use the isNull query?

Is Null | Query.isNull("name") | Returns documents where attribute value is null.
Is Not Null | Query.isNotNull("name") | Returns documents where attribute value is not null.