Datatables Editor PHP SDK: Set default-value on create or when certain field is empty

mattomedia

Hi Guys,

I am struggling to correctly use the Validator and Formatter-Function of the Datatables-Editor PHP SDK.

Use Case:
Generate a new UUID on create of a new Entry or generate a new UUID when the Field uuid is empty.

Shouldn't this code lead to creating a new UUID via rex_yform_value_uuid::guidv4() when the field uuid is empty?

Field::inst( $mainTable.'.uuid' )
->setFormatter( 
    Format::ifEmpty( rex_yform_value_uuid::guidv4() )
    ),

allan

Is rex_yform_value_uuid::guidv4() a PHP function? If so, then yes, what you have should result in a UUID being inserted if the submitted value is an empty string.

If that isn't happening, add the ->debug(true) method just before ->process(...) and then can you show me the SQL that is being generated (shown in the JSON response) and also the data being submitted please?

Thanks,
Allan

mattomedia

HI Allan,

thank you for your answer.

This is the PHP-Function for the UUID:
https://github.com/yakamara/redaxo_yform/blob/49cdff49a7323dc2d963a9640286d75fa5bac5a8/lib/yform/value/uuid.php#L25-L36

Error:

An SQL error occurred: SQLSTATE[HY000]: General error: 1364 Field 'uuid' doesn't have a default value

Query:

"INSERT INTO  `rex_ee_leistungen`  ( `invoice_firma`, `invoice_vorname`, `invoice_nachname`, `invoice_strasse_hnr`, `invoice_plz`, `invoice_ort`, `objekt_strasse_hnr`, `objekt_plz`, `preis`, `objekt_ort`, `bemerkung`, `stichtag`, `objekt_art`, `bearbeiter`, `status` ) VALUES (  :invoice_firma,  :invoice_vorname,  :invoice_nachname,  :invoice_strasse_hnr,  :invoice_plz,  :invoice_ort,  :objekt_strasse_hnr,  :objekt_plz,  :preis,  :objekt_ort,  :bemerkung,  :stichtag,  :objekt_art,  :bearbeiter,  :status )"

Bindings:

[
    {
        "name": ":invoice_firma",
        "value": "test",
        "type": null
    },
    {
        "name": ":invoice_vorname",
        "value": "test",
        "type": null
    },
    {
        "name": ":invoice_nachname",
        "value": "test",
        "type": null
    },
    {
        "name": ":invoice_strasse_hnr",
        "value": "test",
        "type": null
    },
    {
        "name": ":invoice_plz",
        "value": "12345",
        "type": null
    },
    {
        "name": ":invoice_ort",
        "value": "test",
        "type": null
    },
    {
        "name": ":objekt_strasse_hnr",
        "value": "",
        "type": null
    },
    {
        "name": ":objekt_plz",
        "value": "",
        "type": null
    },
    {
        "name": ":preis",
        "value": "5000",
        "type": null
    },
    {
        "name": ":objekt_ort",
        "value": "",
        "type": null
    },
    {
        "name": ":bemerkung",
        "value": "",
        "type": null
    },
    {
        "name": ":stichtag",
        "value": "",
        "type": null
    },
    {
        "name": ":objekt_art",
        "value": "",
        "type": null
    },
    {
        "name": ":bearbeiter",
        "value": "",
        "type": null
    },
    {
        "name": ":status",
        "value": "",
        "type": null
    }
]

allan

Field::inst( $mainTable.'.uuid' )
  ->setFormatter( function ($val) {
    return $val ?
      $val :
      rex_yform_value_uuid::guidv4();
  } )

I'd be tempted to use this rather than ifEmpty. Either should work, but the benefit of this method is that the UUID will only be generated when it is needed. With ifEmpty it would be generated every single time that script runs.

However, as I said, either method should work, so I'm not certain that this will fix the issue. Can you show me your full PHP for Editor?

Allan

mattomedia

Thank you for your effort.
The given code results in the same error:

An SQL error occurred: SQLSTATE[HY000]: General error: 1364 Field 'uuid' doesn't have a default value

This is the PHP-Code for Editor:

Editor::inst(rex_datatables::getDb(), $mainTable)
                    ->field(
                        Field::inst($mainTable.'.id'),
                        Field::inst($mainTable.'.invoice_firma'),
                        Field::inst( $mainTable.'.invoice_vorname' )
                            ->validator( Validate::notEmpty( ValidateOptions::inst()
                                ->message( 'Dies ist ein Pflichtfeld' )
                            ) ),
                        Field::inst($mainTable.'.invoice_nachname')
                            ->validator( Validate::notEmpty( ValidateOptions::inst()
                                ->message( 'Dies ist ein Pflichtfeld' )
                            ) ),
                        Field::inst($mainTable.'.invoice_strasse_hnr')
                            ->validator( Validate::notEmpty( ValidateOptions::inst()
                                ->message( 'Dies ist ein Pflichtfeld' )
                            ) ),
                        Field::inst($mainTable.'.invoice_plz')
                            ->validator( Validate::notEmpty( ValidateOptions::inst()
                                ->message( 'Dies ist ein Pflichtfeld' )
                            ) ),
                        Field::inst($mainTable.'.invoice_ort')->validator( Validate::notEmpty( ValidateOptions::inst()
                            ->message( 'Dies ist ein Pflichtfeld' )
                        ) ),
                        Field::inst($mainTable.'.objekt_strasse_hnr'),
                        Field::inst($mainTable.'.objekt_plz'),
                        Field::inst($mainTable.'.preis'),
                        Field::inst($mainTable.'.objekt_ort'),
                        Field::inst($mainTable.'.bemerkung'),
                        Field::inst($mainTable.'.stichtag'),

                        Field::inst($mainTable.'.createdate')
                            ->validator( Validate::dateFormat( 'd.m.Y' ) )
                            #->getFormatter( Format::dateSqlToFormat( 'd.m.Y' ) )
                            ->setFormatter( Format::ifEmpty(Format::dateFormatToSql('d.m.Y' )) ),

                        Field::inst($mainTable.'.updatedate')
                            ->validator( Validate::dateFormat( 'd.m.Y' ) )
                            ->getFormatter( Format::dateSqlToFormat( 'd.m.Y' ) )
                            ->setFormatter( Format::dateFormatToSql('d.m.Y' ) ),

                        Field::inst( $mainTable.'.uuid' )
                            ->setFormatter( function ($val) {
                                return $val ?
                                    $val :
                                    rex_yform_value_uuid::guidv4();
                            } ),

                        // Join Objektarten
                        Field::inst($mainTable.'.objekt_art')
                            ->options(Options::inst()
                                ->table($joinTableA)
                                ->value('id')
                                ->label('name')
                            ),
                        Field::inst( $joinTableA.'.name' ),

                        // Join Bearbeiter
                        Field::inst($mainTable.'.bearbeiter')
                            ->options(Options::inst()
                                ->table($joinTableB)
                                ->value('id')
                                ->label('intern_bearbeiter_name')
                            ),
                        Field::inst( $joinTableB.'.intern_bearbeiter_name' ),

                        // Join Status
                        Field::inst($mainTable.'.status')
                            ->options(Options::inst()
                                ->table($joinTableC)
                                ->value('id')
                                ->label('name')
                            ),
                        Field::inst( $joinTableC.'.name' ),
                    )

                    // Normale Joins
                    ->leftJoin($joinTableA, $joinTableA.'.id', '=', $mainTable.'.objekt_art')
                    ->leftJoin($joinTableB, $joinTableB.'.id', '=', $mainTable.'.bearbeiter')
                    ->leftJoin($joinTableC, $joinTableC.'.id', '=', $mainTable.'.status')

                    // mJoin A
                    // MJoin Leistungsitems
                    ->join(
                        Mjoin::inst( $mJoinMainTableA )
                            ->link( $mainTable.'.id', $mJoinTableA.'.id_leistung' )
                            ->link( $mJoinMainTableA.'.id', $mJoinTableA.'.id_leistungs_item' )
                            ->order( 'name asc' )
                            ->validator( $mJoinMainTableA.'[].id', Validate::mjoinMinCount(1, 'Bitte mindestens eine Leistung auswählen') )
                            ->fields(
                                Field::inst( 'id' )
                                    ->validator( Validate::required() )
                                    ->options( Options::inst()
                                        ->table( $mJoinMainTableA )
                                        ->value( 'id' )
                                        ->label( 'name' )
                                    ),
                                Field::inst( 'name' )
                            )
                    )
                    // mJoin B
                    // MJoin auf Zusätze
                    ->join(
                        Mjoin::inst( $mJoinMainTableB )
                            ->link( $mainTable.'.id', $mJoinTableB.'.id_leistung' )
                            ->link( $mJoinMainTableB.'.id', $mJoinTableB.'.id_zusatz' )
                            ->order( 'name asc' )
                            ->validator( $mJoinMainTableB.'[].id', Validate::mjoinMaxCount(3, 'No more than four selections please') )
                            ->fields(
                                Field::inst( 'id' )
                                    ->validator( Validate::required() )
                                    ->options( Options::inst()
                                        ->table( $mJoinMainTableB )
                                        ->value( 'id' )
                                        ->label( 'name' )
                                    ),
                                Field::inst( 'name' )
                            )
                    )

                    ->debug(true)
                    ->process($_POST)
                    ->json();

allan

It has just clicked for me that uuid isn't in the submitted data. The setFormatter won't run for that, since nothing was submitted. It also can't tell if there is currently a value or not, again because it wasn't submitted.

Presumably you don't want the end user to be able to change it?

I think what your best option is here, is to use a database trigger. On insert or update, check if it has a value and if not, generate a new uuid value.

If that isn't an option, what you would need to do in this case is use server-side events and on create (preCreate) do:

$editor->field( $mainTable.'.uuid' )->setValue(rex_yform_value_uuid::guidv4());

On edit (preEdit) you would need to make a database query to see if it has a value or not. If not, then generate a value and use setValue again.

This assumes that you might have database entries already which don't have a uuid value. If you either don't have that issue, or there aren't yet any database rows for this table, then you could just use the preCreate event to set the UUID value when a new row is created.

Allan

mattomedia

Great!
I was assuming, that every field in the Editor-Class is always regarded. No matter if data is sent or not.

With the preCreate-Action I am able to generate a new UUID for every new entry. That UUID will never change again. Exactly what I needed.

Thanks so much for the support. Appreciate it!

Keep on doing a great job on datatables and the forum support. Pretty impressive. Thanks!

allan

Thank you

Great to hear that did the job for you.

Allan

mattomedia

On a sidenote.
In the manual for server-side-events is a dead link for the example.
The Description of the example-code for logging changes mentions for Line 2 the Database->insert-Method, which is linked to a 404, but propably moved to here:

https://editor.datatables.net/docs/2.1.0/php/classes/DataTables-Database.html#method_insert

allan

Thanks for pointing that out! I updated to a different documentation generator and missed that link. Fixed locally and I'll deploy the change soon.

Allan