Bug #76701

Not possible to insert NULL value with TCA type 'select'

Added by Viktor Livakivskyi about 3 years ago. Updated over 2 years ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
FormEngine aka TCEforms
Target version:
-
Start date:
2016-06-20
Due date:
% Done:

0%

TYPO3 Version:
7
PHP Version:
5.6
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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.

History

#1 Updated by Morton Jonuschat about 3 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.

#2 Updated by Viktor Livakivskyi about 3 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.

#3 Updated by Anja Leichsenring about 3 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.

#4 Updated by Viktor Livakivskyi about 3 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?

#5 Updated by Harald Glaser almost 3 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?

#6 Updated by Viktor Livakivskyi almost 3 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.

#7 Updated by Alexander Opitz over 2 years ago

  • Status changed from Needs Feedback to New

Also available in: Atom PDF