Issue in getting Join to work with Editor

johnhpe

I have been looking at the example https://editor.datatables.net/examples/simple/join.html to get a left join to work with MYSQL and Editor. A really great example, that I have followed more or less to the letter with the exception of using bootstrap. It all works fine until you try and ADD or EDIT a row. The correct modal pops up and the Fields->options select box works great. No errors and nothing added / changed.

Serverside:

<?php
include( "../JS/Editor-PHP-1.9.0/lib/DataTables.php" );

    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;

    $editor = Editor::inst( $db, 'mgp.vlans', 'id')
        ->field(
            Field::inst( 'vlans.id' ),
            Field::inst( 'vlans.name' ),
            Field::inst( 'vlans.description' ),
            Field::inst( 'vlans.switch_id')
                ->options( Options::inst()
                ->table( 'switches' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( 'Validate::dbValues' ),
            Field::inst( 'switches.name' )
        )
        ->leftJoin ('switches','switches.id','=','vlans.switch_id')
        ->debug(true)
        ->process($_POST)
        ->json();

<?php
>
```
Javascript
```
$(document).ready( function () {
    
    var editor;
        
    editor = new $.fn.dataTable.Editor( {
        ajax: "./ajax/vlans.php",
        table: "#myTable",
        fields: [ 
            {
                label: "Vlan ID:",
                name: "vlans.id"
            }, {
                label: "Name:",
                name: "vlans.name"
            }, {
                label: "Description:",
                name: "vlans.description"
            },
            {
                label: "Switch:",
                name: "vlans.switch_id",
                type: "select"
            }
        ],
    } );
?>


    $('#myTable').DataTable( {
        dom: 'Blfrtip',
        select: true,
        ajax: { "url" :"./ajax/vlans.php", type: "POST" },
        lengthMenu: [ [15, 30, 50, 100, -1], [15, 30, 50, 100, "All"] ],
        pageLength: 15,
        "columns": [ 
            { data : "vlans.id" },
            { data : "vlans.name"},
            { data : "vlans.description"},
            { data : "switches.name" }
        ],
        buttons: [ 
            { extend: 'create', editor: editor, className: 'btn-sm' },
            { extend: 'edit', editor: editor, className: 'btn-sm' },
            { extend: "remove", className: 'btn-sm btn-danger', editor: editor, formMessage: function( e, dt ){ return 'Are you sure you want to delete the selected '+dt.rows({selected:true}).count()+' queries?'; } },
            { extend: 'copy', className: 'btn-sm'}, 
            { extend: 'csv',  className: 'btn-sm'},
            { extend: 'excel', className: 'btn-sm'}
        ]
    });
    $('div.dataTables_length select').addClass('js-example-basic-single');
    $(".js-example-basic-single").select2({minimumResultsForSearch: -1});
} );

I have put debug on and have the the correct headers of change going to the script (name now = New Name)

action: edit
data[row_4][vlans][id]: 4
data[row_4][vlans][name]: New Name
data[row_4][vlans][description]: Server_U4
data[row_4][vlans][switch_id]: 5

And here is the response:

{"data":[{"DT_RowId":"row_4","vlans":{"id":"4","name":"","description":"Server_U4","switch_id":"5"},"switches":{"name":"Test1"}}],"debug":[{"query":"SELECT  `id` as 'id' FROM  `switches` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"5","type":null}]},{"query":"SELECT  `mgp`.`vlans`.`id` as 'mgp.vlans.id', `vlans`.`id` as 'vlans.id', `vlans`.`name` as 'vlans.name', `vlans`.`description` as 'vlans.description', `vlans`.`switch_id` as 'vlans.switch_id', `switches`.`name` as 'switches.name' FROM  `mgp`.`vlans` LEFT JOIN `switches` ON `switches`.`id` = `vlans`.`switch_id` WHERE `mgp`.`vlans`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"4","type":null}]}]}

I've been looking at this for hours now - help most appreciated.....

Nathan

johnhpe

So I found the problem at last. Although this works fine without joins the problem was in the creation of EDITOR in the serverside script. I included the database name with the table name. Removed DB name and all ok now.

$editor = Editor::inst( $db, 'mgp.vlans', 'id') 

becomes

Editor::inst( $db, 'vlans', 'id')

allan

Hi,

Thanks for the follow up. Great to hear you managed to identify the issue. I'll look into the table / db names at our end and see if I can improve our handling of that for joins.

Thanks,
Allan