Adding Options to select2 in Editor form

Manoel Waller

Hi

I have a database structure that looks like this:

The Editor form should show the records from the subtables as options of select2-inputs. When adding or updating a record of the main table you can either choose from the given options or add new option which is displayes as "... (new)". When the submit button is pressed, the field inputs get validated and formatted and if the option is new (not existing in the subtable) it gets inserted via the custom Validator and the id of the new option is returned by the custom Formatter. Then the form closes and the new or updated record (maintable) with the new optioin is displayed. So this works perfectly fine. But ...

When I add or update another record and want to use the new option that was inserted in one of the subtables in the previous step, this option wont show up in the select2-input until the page gets reloaded.

So my question is: "Why is the new option showing up in the datatable but not in the select2?"
Is there anything I'm doing wrong?
Or is ther a better way to do it?

The setup of the editor instance for "maintable" looks like this:

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/test_join.php",
        table: "#maintable",
        fields: [ {
                label: "Subtable1:",
                name: "maintable.sub1_id",
                type: "select2",
                opts: {
                    debug: true,
                    tags: true,
                    createTag: function (tag) {
                        return {
                            id: tag.term,
                            text: tag.term + " (neu)",
                            tag: true
                        };
                    }
                }
            }, {
                label: "Subtable2:",
                name: "maintable.sub2_id",
                type: "select2",
                opts: {
                    tags: true,
                    createTag: function (tag) {
                        return {
                            id: tag.term,
                            text: tag.term + " (neu)",
                            tag: true
                        };
                    }
                }
            }, {
                label: "Subtable3:",
                name: "maintable.sub3_id",
                type: "select2",
                opts: {
                    tags: true,
                    createTag: function (tag) {
                        return {
                            id: tag.term,
                            text: tag.term + " (neu)",
                            tag: true
                        };
                    }
                }
            }
        ]
    } );

    $('#maintable').DataTable( {
    responsive: true,
    "lengthMenu": [ [10, 25, 50, 100, -1], [10, 25, 50, 100, "All"] ],
    dom: "Bfrtlp",
    ajax: {
        url: "../php/test_join.php",
        type: 'POST'
    },
    columns: [
        { data: "maintable.id" },
        { data: "subtable1.name_sub1" },
        { data: "subtable2.name_sub2" },
        { data: "subtable3.name_sub3" }
    ],
    select: true,
    buttons: [
        {
            extend: "create",
            text: "Neu",
            editor: editor,
            formButtons: [
                'Annehmen',
                { label: 'Abbrechen', fn: function () { this.close(); } }
            ]
        },
        {
            extend: "edit",
            text: "Bearbeiten",
            editor: editor,
            formButtons: [
                'Annehmen',
                { label: 'Abbrechen', fn: function () { this.close(); } }
            ]
        },
        {
            extend: "remove",
            text: "Löschen",
            editor: editor,
            formButtons: [
                'Löschen',
                { label: 'Abbrechen', fn: function () { this.close(); } }
            ]
        }
    ]
    } );
} );

editor.close();

This is the file to join the Tables:

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'maintable' )
    ->fields(
        Field::inst( 'maintable.id' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'maintable.sub1_id' )
            ->validator( 'Validate::selectDB' )
            ->setFormatter( 'Format::selectDB', 'subtable1')
            ->options( Options::inst()
                ->table( 'subtable1' )
                ->value( 'id_sub1' )
                ->label( 'name_sub1' )
            ),
        Field::inst( 'subtable1.name_sub1' ),
        
        Field::inst( 'maintable.sub2_id' )
            ->validator( 'Validate::selectDB' )
            ->setFormatter( 'Format::selectDB', 'subtable2')
            ->options( Options::inst()
                ->table( 'subtable2' )
                ->value( 'id_sub2' )
                ->label( 'name_sub2' )
            ),
        Field::inst( 'subtable2.name_sub2' ),
        
        Field::inst( 'maintable.sub3_id' )
            ->validator( 'Validate::selectDB' )
            ->setFormatter( 'Format::selectDB', 'subtable3')
            ->options( Options::inst()
                ->table( 'subtable3' )
                ->value( 'id_sub3' )
                ->label( 'name_sub3' )
            ),
        Field::inst( 'subtable3.name_sub3' )
    )
    ->leftJoin( 'subtable1', 'subtable1.id_sub1', '=', 'maintable.sub1_id' )
    ->leftJoin( 'subtable2', 'subtable2.id_sub2', '=', 'maintable.sub2_id' )
    ->leftJoin( 'subtable3', 'subtable3.id_sub3', '=', 'maintable.sub3_id' )
    ->process( $_POST )
    ->json();

<?php
>
```
?>


The following code shows my custom Validator:

public static function selectDB ( $val, $data, $opts, $host ) {

    $cfg = Validate::_extend( $opts, null, array(
    'message' => "This input must be given as a number",
    'decimal' => '.',
    'db'      => null,
    'table'   => null,
    'field'   => null
    ) );

    $common = Validate::_common( $val, $cfg );
    if ( $common !== null ) {
        return $common; 
    }

    if ( $cfg['decimal'] !== '.' ) {
        $val = str_replace( $cfg['decimal'], '.', $val );
    }

    $editor = $host['editor'];
    $field = $host['field'];
    $options = $field->options();

    $db = $cfg['db'] ? $cfg['db'] : $host['db'];
    $tab = $cfg['table'] ? $cfg['table'] : $options->table();

    if($tab == "subtable1"){$name = "name_sub1";}
    else if($tab == "subtable2"){$name = "name_sub2";}
    else if($tab == "subtable3"){$name = "name_sub3";}

    if (!is_numeric( $val ))
    {

            $dbnew=mysqli_connect("localhost","root","","testdb");
            $sql = "INSERT INTO ".$tab." (".$name.") VALUES ('".$val."');";
            mysqli_query($dbnew,$sql);  // Select Befehl ausführen
            mysqli_close($dbnew);

    }

    return true;
}

And last but not least there is my custom Formatter:

public static function selectDB ( $val, $data, $opts ) {
    if (is_numeric( $val ))
    {
        return $val;
    }
    else
    {   
        if($opts == "subtable1"){$name = "name_sub1";}
        else if($opts == "subtable2"){$name = "name_sub2";}
        else if($opts == "subtable3"){$name = "name_sub3";}

            $dbnew=mysqli_connect("localhost","root","","testdb");
            $sql = "SELECT * FROM ".$opts." WHERE ".$name." = '".$val."';";
            $result=mysqli_query($dbnew,$sql);  // Select Befehl ausführen
            $row=mysqli_fetch_array($result);
            $id=$row[0];
            mysqli_close($dbnew);
            return $id;

    }
}

In case you want to recreate my db-structure here is the code for the creation of the tables with some test records:

CREATE TABLE subtable1 (
id_sub1 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name_sub1 VARCHAR(255) NULL,
PRIMARY KEY(id_sub1)
);

CREATE TABLE subtable2 (
id_sub2 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name_sub2 VARCHAR(255) NULL,
PRIMARY KEY(id_sub2)
);

CREATE TABLE subtable3 (
id_sub3 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name_sub3 VARCHAR(255) NULL,
PRIMARY KEY(id_sub3)
);

CREATE TABLE maintable (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
sub1_id INTEGER UNSIGNED NOT NULL,
sub2_id INTEGER UNSIGNED NOT NULL,
sub3_id INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX Absolventen_FKIndex1(sub1_id),
INDEX Absolventen_FKIndex2(sub2_id),
INDEX Absolventen_FKIndex3(sub3_id),
FOREIGN KEY(sub1_id)
REFERENCES subtable1(id_sub1)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(sub2_id)
REFERENCES subtable2(id_sub2)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(sub3_id)
REFERENCES subtable3(id_sub3)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

INSERT INTO subtable1 (name_sub1) VALUES ("sub1_name1");
INSERT INTO subtable1 (name_sub1) VALUES ("sub1_name2");
INSERT INTO subtable1 (name_sub1) VALUES ("sub1_name3");
INSERT INTO subtable1 (name_sub1) VALUES ("sub1_name4");
INSERT INTO subtable1 (name_sub1) VALUES ("sub1_name5");

INSERT INTO subtable2 (name_sub2) VALUES ("sub2_name1");
INSERT INTO subtable2 (name_sub2) VALUES ("sub2_name2");
INSERT INTO subtable2 (name_sub2) VALUES ("sub2_name3");
INSERT INTO subtable2 (name_sub2) VALUES ("sub2_name4");
INSERT INTO subtable2 (name_sub2) VALUES ("sub2_name5");

INSERT INTO subtable3 (name_sub3) VALUES ("sub3_name1");
INSERT INTO subtable3 (name_sub3) VALUES ("sub3_name2");
INSERT INTO subtable3 (name_sub3) VALUES ("sub3_name3");
INSERT INTO subtable3 (name_sub3) VALUES ("sub3_name4");
INSERT INTO subtable3 (name_sub3) VALUES ("sub3_name5");

INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (1,4,2);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (2,5,3);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (4,2,3);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (2,3,4);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (1,2,3);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (2,3,5);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (5,2,1);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (4,2,3);
INSERT INTO maintable (sub1_id, sub2_id, sub3_id) VALUES (2,3,1);
```

I hope you can help me.

Manoel

allan

What should be happening is that when you make either an edit or create request with Editor, it will do whatever is needed to update the database and then read the data back, including the options.

So the first thing to establish is if the JSON returned from the create or edit request does actually include the new option in the options object. Could you confirm if it does?

Thanks,
Allan

Manoel Waller

Hey Allan

Thanks for the quick reply.
As you suggested i created a new record with 3 new options and got the following JSON response:
{"data":[{"DT_RowId":"row_11","maintable":{"id":"11","sub1_id":"11","sub2_id":"6","sub3_id":"7"},"subtable1":{"name_sub1":"TEST3"},"subtable2":{"name_sub2":"Test"},"subtable3":{"name_sub3":"test"}}]}
So you can see the IDs of the newly generated options (11, 6, 7) and their names (TEST3, Test, test).
The options object is only shown after the page gets refreshed, which you can see here:
{"data":[{"DT_RowId":"row_1","maintable":{"id":"1","sub1_id":"8","sub2_id":"4","sub3_id":"2"},"subtable1":{"name_sub1":"TEST1"},"subtable2":{"name_sub2":"sub2_name4"},"subtable3":{"name_sub3":"sub3_name2"}},{"DT_RowId":"row_2","maintable":{"id":"2","sub1_id":"2","sub2_id":"5","sub3_id":"3"},"subtable1":{"name_sub1":"sub1_name2"},"subtable2":{"name_sub2":"sub2_name5"},"subtable3":{"name_sub3":"sub3_name3"}},{"DT_RowId":"row_3","maintable":{"id":"3","sub1_id":"4","sub2_id":"2","sub3_id":"3"},"subtable1":{"name_sub1":"sub1_name4"},"subtable2":{"name_sub2":"sub2_name2"},"subtable3":{"name_sub3":"sub3_name3"}},{"DT_RowId":"row_4","maintable":{"id":"4","sub1_id":"2","sub2_id":"3","sub3_id":"4"},"subtable1":{"name_sub1":"sub1_name2"},"subtable2":{"name_sub2":"sub2_name3"},"subtable3":{"name_sub3":"sub3_name4"}},{"DT_RowId":"row_5","maintable":{"id":"5","sub1_id":"1","sub2_id":"2","sub3_id":"3"},"subtable1":{"name_sub1":"sub1_name1"},"subtable2":{"name_sub2":"sub2_name2"},"subtable3":{"name_sub3":"sub3_name3"}},{"DT_RowId":"row_6","maintable":{"id":"6","sub1_id":"2","sub2_id":"3","sub3_id":"5"},"subtable1":{"name_sub1":"sub1_name2"},"subtable2":{"name_sub2":"sub2_name3"},"subtable3":{"name_sub3":"sub3_name5"}},{"DT_RowId":"row_7","maintable":{"id":"7","sub1_id":"5","sub2_id":"2","sub3_id":"1"},"subtable1":{"name_sub1":"sub1_name5"},"subtable2":{"name_sub2":"sub2_name2"},"subtable3":{"name_sub3":"sub3_name1"}},{"DT_RowId":"row_8","maintable":{"id":"8","sub1_id":"4","sub2_id":"2","sub3_id":"3"},"subtable1":{"name_sub1":"sub1_name4"},"subtable2":{"name_sub2":"sub2_name2"},"subtable3":{"name_sub3":"sub3_name3"}},{"DT_RowId":"row_9","maintable":{"id":"9","sub1_id":"2","sub2_id":"3","sub3_id":"1"},"subtable1":{"name_sub1":"sub1_name2"},"subtable2":{"name_sub2":"sub2_name3"},"subtable3":{"name_sub3":"sub3_name1"}},{"DT_RowId":"row_10","maintable":{"id":"10","sub1_id":"10","sub2_id":"2","sub3_id":"3"},"subtable1":{"name_sub1":"TSET3"},"subtable2":{"name_sub2":"sub2_name2"},"subtable3":{"name_sub3":"sub3_name3"}},{"DT_RowId":"row_11","maintable":{"id":"11","sub1_id":"11","sub2_id":"6","sub3_id":"7"},"subtable1":{"name_sub1":"TEST3"},"subtable2":{"name_sub2":"Test"},"subtable3":{"name_sub3":"test"}}],"options":{"maintable.sub1_id":[{"label":"TEST","value":"6"},{"label":"TEST1","value":"8"},{"label":"TEST2","value":"9"},{"label":"TEST3","value":"11"},{"label":"TSET","value":"7"},{"label":"TSET3","value":"10"},{"label":"sub1_name1","value":"1"},{"label":"sub1_name2","value":"2"},{"label":"sub1_name3","value":"3"},{"label":"sub1_name4","value":"4"},{"label":"sub1_name5","value":"5"}],"maintable.sub2_id":[{"label":"Test","value":"6"},{"label":"sub2_name1","value":"1"},{"label":"sub2_name2","value":"2"},{"label":"sub2_name3","value":"3"},{"label":"sub2_name4","value":"4"},{"label":"sub2_name5","value":"5"}],"maintable.sub3_id":[{"label":"Test","value":"6"},{"label":"sub3_name1","value":"1"},{"label":"sub3_name2","value":"2"},{"label":"sub3_name3","value":"3"},{"label":"sub3_name4","value":"4"},{"label":"sub3_name5","value":"5"},{"label":"test","value":"7"}]},"files":[]}

I hope I did not misunderstand your post because I am pretty new to all that JSON stuff.
Is that what you meant?

Manoel

allan

Hi Manoel,

Perfect - thanks. It looks like I mis-remembered exactly how the Editor code operates in that circumstance.

If you open the file php/Editor/Editor.php, in the _get method you will find this block of code:

        if ( $id === null ) {
            foreach ($this->_fields as $field) {
                $opts = $field->optionsExec( $this->_db );

                if ( $opts !== false ) {
                    $options[ $field->name() ] = $opts;
                }
            }
        }

If you remove the first and last lines of that block (i.e. remove the if statement), it will update the options when editing data.

Regards,
Allan

Manoel Waller

Hi Allan

Thanks again for that quick response.
I edited the code in Editor.php as you said:

        //if ( $id === null ) {
            foreach ($this->_fields as $field) {
                $opts = $field->optionsExec( $this->_db );

                if ( $opts !== false ) {
                    $options[ $field->name() ] = $opts;
                }
            }
        //}

I also tried removing the lines completely but unfortunately it did not make any differnence. So i still got exactly the same problem.

Do you have any other ideas?

Manoel

allan

Darn - sorry. You are absolutely correct, that won't work because of how the data object is built for the edited data. Sorry I didn't realise that before.

At the moment what I think you'll need to do is:

editor.on( 'submitComplete', function () {
  table.ajax.reload( null, false );
} );

Its not ideal I realise, but there isn't a quick hack in the PHP script to make it work after all. I'll have a think about how best to handle this in the libraries.

Regards,
Allan

Manoel Waller

Hey Allan

Thank you very very much.
It works exactly as it should.

Regards,
Manoel