Issue in getting Join to work with Editor

Issue in getting Join to work with Editor

johnhpejohnhpe Posts: 9Questions: 2Answers: 0

I have been looking at the example 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.


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


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


$(document).ready( function () {
    var editor;
    editor = new $.fn.dataTable.Editor( {
        ajax: "./ajax/vlans.php",
        table: "#myTable",
        fields: [ 
                label: "Vlan ID:",
                name: ""
            }, {
                label: "Name:",
                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 : "" },
            { data : ""},
            { data : "vlans.description"},
            { data : "" }
        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 '', `vlans`.`id` as '', `vlans`.`name` as '', `vlans`.`description` as 'vlans.description', `vlans`.`switch_id` as 'vlans.switch_id', `switches`.`name` as '' 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.....



  • johnhpejohnhpe Posts: 9Questions: 2Answers: 0

    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') 


    Editor::inst( $db, 'vlans', 'id')
  • allanallan Posts: 50,047Questions: 1Answers: 7,403 Site admin


    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.


Sign In or Register to comment.