Bug #76701
openNot possible to insert NULL value with TCA type 'select'
0%
Description
Hi,
I want a possibility to insert NULL
via BE into a field of type "select".
My TCA definition is follwoing:
'industry' => [ 'exclude' => 0, 'label' => 'LLL:EXT:my_ext/Resources/Private/Language/locallang_db.xlf:fe_users.industry', 'config' => [ 'type' => 'select', 'renderType' => 'selectSingle', 'foreign_table' => 'tx_myext_domain_model_industry', 'minitems' => 0, 'items' => [ ['', null], ] ] ],
ext_tables.sql:industry int(11) unsigned default NULL,
The problem is, that whenever an empty value from "industry" is sent to a server it is translated into following query:UPDATE fe_users SET industry='',tstamp='1465920150' WHERE uid=2;
And due to disabled "strict mode" it translates to 0 by MySQL.
Also tried to set '-1' as drop-down option value, but result is the same.
I guess, it should be some fields processor, which explicitly sets NULL
for fields, which desire it.
Updated by Morton Jonuschat about 8 years ago
- Status changed from New to Needs Feedback
HTML itself cannot transport a "null" value. Depending on context and representation the form field will either not be transmitted at all (like an unchecked checkbox) or converted to an empty string.
If you want to store NULL in your Database with data coming from any form you will need to manually post-process the data before writing it to the database, for example using the processDatamap_postProcessFieldArray hook. There you can check if the value is an empty string (or any other value you use to mark "unselected" entries) and replace it with NULL.
Updated by Viktor Livakivskyi about 8 years ago
Hi, Morton.
Thank you for your feedback. Yes, this is exactly how I do it at the moment, but I though, that such functionality should exist in core.
Just for example, with the setup above, when an empty value is submitted via BE, the drop-down shows then following items:
- [ INVALID VALUE ("0") ]
-
- Value 1
- Value 2
...
If it is not planned to include such processing in core, then it makes sense to mention in documentation, that null values are not possible unless developer implements processDatamap_postProcessFieldArray hook.
Updated by Anja Leichsenring about 8 years ago
Hi Viktor,
there is actually nothing the Core can do about, because we can not influence the behaviour of HTML. There simply isn't such a thing as a representation of `null`. And on the PHP side of things, the developer has to decide, what would represent a null value to base the post-processing upon, we can not just implement any of those many possibilities. I for my part use the string 'thisIsANullValue' in my HTML structures and translate it to null in my post-processing. For sure nothing you want to find in the core. So what should we do? Improving the documentation, providing an example how to use the hook Morton mentioned. That seems the only valid solution to me. Do you agree?
In case you can actually provide a code wise solution, that satisfies all use cases and doesn't break existing code, I would happily integrate it into the core.
If you agree with the documentation approach, maybe you want to provide your solution? Then at least it is your code and description providing the wanted guidance.
Updated by Viktor Livakivskyi about 8 years ago
Hi, Anja.
Thank you for an answer. Yes, there is no difference between null
and empty value in terms of HTTP GET/POST.
But we can analyze corresponding db column (this doesn't even need to be done on DataHandler level, but on DatabaseConnection) and if it is NULL-able (or/and null
is default value), then convert empty value to null
.
Example from my hook:
public function processDatamap_postProcessFieldArray($status, $table, $id, &$fieldArray, &$reference) { $fieldInfo = $GLOBALS['TYPO3_DB']->admin_get_fields($table); foreach ($fieldArray as $fieldName => $fieldValue) { if (empty($fieldValue) && $fieldInfo[$fieldName]['Null'] == "YES" ) { $fieldArray[$fieldName] = null; } } }
Sure, such hook can cause extra load on modification of data with lot of IRRE records edited/added, therefore some caching may be added here, if needed (like currently core caches TCA definitions).
But I still think, this should be job of core - not each individual dev.
Reasons why:
1. Consistency. Currently behavior is inconsistent: in case of integer nullable drop-down - 0 is written, but in case of varchar nullable input - real null
is written and not an empty string.
2. Value transformation on side of TYPO3 Core rather than relying on MySQL internal rules of non-strict mode. So, in case next MySQL version changes it's rules, no installation will be affected by this.
2b. Being one step closer to support "strict mode" in MySQL.
3. Less non-intuitive job for a developer. I don't see a scenario, when developer declares a field as default NULL
in ext_tables.sql, but wants it to be able to hold empty value, when it is passed (which due to disabled "strict mode" is transformed to 0 by MySQL).
Does it make sense?
Updated by Harald Glaser almost 8 years ago
Hi Viktor!
I totally agree with you that this is an important feature (e.g. it makes it possible to use foreign keys in database definitions for optional fields) and that it should be handled in the core.
In regard to backwards-compatibility and to minimize the additional load maybe an opt-in version via TCA makes sense - inspired by your code I use the following postprocess-hook:
public function processDatamap_postProcessFieldArray($status, $table, $id, &$fieldArray, &$reference) { foreach ($fieldArray as $fieldName => $fieldValue) { if (empty($fieldValue) && !!$GLOBALS['TCA'][$table]['columns'][$fieldName]['config']['emptyAsNull']) { $fieldArray[$fieldName] = null; } } }
So as a disadvantage I have to configure one additional value in TCA for each field, but on the other hand I guess looking up the value in TCA should be faster on the long run and adding the behaviour to the core should be easier.
What do you / everyone think?
Updated by Viktor Livakivskyi almost 8 years ago
Hi, Harald.
I was also thinking about similar solution, but didn't want to invent additional TCA option - wanted to keep it semi-automatic, therefore involved admin_get_fields()
here.
Updated by Alexander Opitz over 7 years ago
- Status changed from Needs Feedback to New
Updated by Susanne Moog over 4 years ago
- Category changed from FormEngine aka TCEforms to DataHandler aka TCEmain
Updated by Christian Weiske 2 months ago
ยท Edited
This is still a problem with TYPO3 v11 and later.
It could be solved by supporting the "eval" property with select fields, and its "null" value that is already available for e.g. the text input type: https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/ColumnsConfig/Type/Input/Properties/Eval.html
But since with #97384 the "nullable" TCA property was introduced, so it should be added to the select field, too (instead of eval=null).
When "nullable" is activated, the fields have a "Set value" checkbox, which allows both NULL values for input fields as well as empty strings.
So simply reusing nullable
to convert empty values to null
does not work.
A new configuration property should be introduced, just like Harald Glaser suggested: emptyAsNull
.
Updated by Christian Weiske 2 months ago
- Related to Task #97384: Use TCA option "nullable" instead of eval=null added