NULL values for numeric fields

248 Posts
Giuse
5 years ago
4
Topic

Hi, to properly use search and filters on a numeric field, the DB column must be declared as INT (or its variations, SMALLINT, etc. etc.) while the Seblod field is a text one. Easy example a field "price".

Now the question is: when that field has no value (e.g. the price is still unknown), so the text field value is an empty string, Seblod does not store NULL (of course the DB column has the NULL attribute), but... the value 0, that is wrong (it's not free :)).

How to tell Seblod to store NULL when a value is empty? Of course the word "NULL" in the default box of the field definition is not a good idea...

Note that if the DB column would be VARCHAR, then Seblod stores an empty string that is fine, but a VARCHAR field is not usable for a numeric field...

thanks

Giuse

Get a Book for SEBLOD
248 Posts
Giuse
5 years ago
0
Level 1

Maybe a github requets to have NULL as a default in addition to "real" values?

248 Posts
Giuse
4 years ago
2
Level 1

I later on made a github request for that, but the bigger problem is that I cannot find a good workaround for that.

Imagine you need a numeric field (quite often situation): the Seblod field is a text one (there is no numeric field) but the DB column is created as a number (e.g. a float).

The problem is when a field value is not filled in the content form: Seblod would store the zero value in the DB that is wrong since an empty value should be stored as NULL number, not as zero.

Waiting for a possible future native feature on this, I created a beforeStore field that when intercepts a field with empty value, it changes the value to the NULL value.

$fields['myfield']->value = NULL;
$config['storages'][$fields['myfield']->storage_table][$fields['myfield']->storage_field] = NULL;


Very strangely this works only on creation and not on edit: when I create a new content item and leave the numeric field empty, the beforeStore does its job and a NULL value is stored into the DB instead of a zero.

BUT... if I edit that item, fill the the field with any value like 1, save the item, then edit again the item clearing the value in the form: the beforeStore field replaces the empty string with a NULL value but Seblod does not store that NULL and leaves the 1 in the DB...

I checked with error_log tracing that beforeStore is correctly executed.

How to tell Seblod to store a NULL value for a numeric field?

Thanks a lot

Giuse

4229 Posts
Kadministrator
4 years ago
1
Level 2

Probably some logic that expects that form value can't be null after it has a value. Workaround would be to use empty string like "" and then in afterStore run some query that sets all empty strings in column to NULL like

UPDATE your_table SET    your_column = NULL WHERE  your_column = '';
248 Posts
Giuse
4 years ago
0
Level 3

Ok, I will do with an afterStore field, I edited the Github issue asking that Seblod stores "NULL" and not "0" into a numeric field where the value is empty., I hope it will be taken into consideration since optional numeric fields are widely used.

thanks

Giuse

Get a Book for SEBLOD