1 datatable and 4 mysql tables

Olan

I have 1 datatables with data coming from 4 mysql tables. I managed to see all data in my datatable but i'm not able to update data in the joined tables. The main tables updates fine with references to data in the joined tables.
Is this even possible?

I use server side processing with leftjoins...

nigel pasco

Are you able to put up a test case for us to have a look at?

Olan

Sure!

PHP:

<?php
    
include( "../DataTables-1.10.2/extensions/Editor-1.3.2/php/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

$data = Editor::inst( $db, 'Member' )
    ->pkey( 'memberKey' )
    ->field(
    Field::inst( 'User.name' ),
    Field::inst( 'Member.label' ),
    Field::inst( 'Member.description' ),
    Field::inst( 'User.email' ),
    Field::inst( 'Member.symbol' ),
    Field::inst( 'Member.teamKey' ),
    Field::inst( 'Member.teamleader' ),
    Field::inst( 'Team.name' ),
    Field::inst( 'Discipline.icon' )
    )
    ->leftJoin( 'Team', 'Team.teamKey', '=', 'Member.teamKey')
    ->leftJoin( 'User', 'User.userKey', '=', 'Member.userKey')
    ->leftJoin( 'Discipline', 'Discipline.name', '=', 'Member.symbol')
    ->process($_POST)
    ->data(); 

if ( ! isset($_POST['action']) ) {
    // Get a list of types for the `select` list
    $data['Team'] = $db
        ->selectDistinct( 'Team', 'teamKey as value, name as label' )
        ->fetchAll();
        
    $data['Discipline'] = $db
        ->query( 'select', 'Discipline' )
                ->get( 'name as value, name as label' )
                ->where( 'name','%_alert%','NOT LIKE' )
                ->exec()
                ->fetchAll();
}
 
echo json_encode( $data );

Javascript:

var editor;
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "table.User.php",
        table: "#Member",
        fields: [{
                label: "User:",
                name: "User.name"
            },
            {
                label: "Label:",
                name: "Member.label"
            },
            {
                label: "Description:",
                name: "Member.description"
            },
            {
                label: "Email:",
                name: "User.email"
            },
            {
                label: "Discipline:",
                name: "Member.symbol",
                type: "select"
            },
            {
                label: "Team:",
                name: "Member.teamKey",
                type: "select"
            },
            {
                label: "Teamleader:",
                name: "Member.teamleader",
                type: "select",
                ipOpts: [
                    {
                        "label": "Yes",
                        "value": "1"
                    },
                    {
                        "label": "No",
                        "value": "0"
                    }
                ]

            }    
        ]
    } );

    editor.on( 'onInitEdit', function () {
    } );
 
    $('#Member').DataTable( {
        dom: "Tfrtip",
        order: [[ 0, "asc" ]],
        ajax: "table.User.php",
        columns: [
            { data: "User.name" },
            { data: "Member.label" },
            { data: "Member.description" },
            { data: "User.email" },
            { data: "Discipline.icon",
              mRender: function ( data ) {
                return '<img src="../images/'+data+'" width="30" height="30"></>';
              }
            },
            { data: "Team.name" },
            { data: "Member.teamleader",
              mRender: function(data) {
                if (data === '1') {
                    return 'Yes';
                } else {
                    return 'No';
                }
              }
            }
        ],
        tableTools: {
            sRowSelect: "os",
            sSwfPath: "../DataTables-1.10.2/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor },
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        },
        initComplete: function ( settings, json ) {
            editor.field( 'Member.teamKey' ).update( json.Team );
            editor.field( 'Member.symbol' ).update( json.Discipline );
        }
    } );
} );

Thanks!

allan

That does look like it should work okay as you have it. So the Member table is being updated, but User is not - is that correct?

Are you receiving any errors from the PHP when you try to do an update?

You might also need to include User.userKey and Member.userKey has hidden fields in the field list (both in the data read and the fields that Editor submits). But I would expect that to give an error in 1.3.2 if the join fields don't exist!

Regards,
Allan

Olan

Thanks Allen!
The Member table was being updated but the User table not.
Including User.userKey and Member.userKey as hidden fields in both the Editor submit and in the data read fields did the trick. No errors but this is maybe because I did a suggested fix I found on teh Forum. In DataTables-1.10.2/extensions/Editor-1.3.2/php/Editor/Editor.php at line 1021 I have now:

if ( $parentLink === $this->_pkey ) {
                $where = array( $childLink => $id );
            }
            else {
                $field = $this->_find_field( $parentLink, 'db' );
                if ( ! $field ) {
                    continue;
                }
                $where = array( $childLink => $field->val('set', $this->_formData) );
            }

Thanks for the help!

nigel pasco

I have a couple tables with a similar issue. The editor buttons work fine when only the primary table is called in the fields (eg. "member.userkey") but it raises the "An error has occurred - Please contact the system administrator" error if I replace this field with a field from the joined table (eg. "user.name" ).

Interesting, in my case, the editor_update does work (on refreshing the page from the error message), but the error comes up anyhow.

I have included the unique keys from the joined tables, and checked all fields exist. I have been playing around with it a bit this evening without much luck I am afraid :(

Olan

Nigel,
Did you tried the fix I mentioned above?

Olan

nigel pasco

Yes, thank you for that. I still am having my issue, but I will spend some more time picking through it.
Good luck with your project.
nige

allan

No errors but this is maybe because I did a suggested fix [...]

Agreed. That is the danger of that fix. It is the right thing to do in some cases, but at other times the error that would have been generated is quite useful...

An error has occurred - Please contact the system administrator

That's a JSON error. What has been returned rather than valid JSON?

Allan

Olan

Thanks Nigel.
Still truggeling with this tabel....
I added a extra tabel to it were i needed a join array to display my data. No errors and the table displayes almost the correct data.
When I edit a row it also messes up the Team table by deleting the name from it.

the tables i used for the join array looks like:

Member table:

memberKey

TeamLink table:

memberKey|teamKey

Team table:

teamKey|name

the code i have now is:

var editor;
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "table.User.php",
        table: "#Member",
        fields: [{
                label: "User:",
                name: "User.name"
            },
            {
                label: "Label:",
                name: "Member.label"
            },
            {
                label: "Description:",
                name: "Member.description"
            },
            {
                label: "Email:",
                name: "User.email"
            },
            {
                label: "Discipline:",
                name: "Member.symbol",
                type: "select"
            },
            {
                label: "Team:",
                name: "Member.teamKey",
                type: "select"
            },
            {
                label: "Teamleader:",
                name: "Member.teamleader",
                type: "select",
                ipOpts: [
                    {
                        "label": "Yes",
                        "value": "1"
                    },
                    {
                        "label": "No",
                        "value": "0"
                    }
                ]

            },    
            {
                label: "User.userKey:",
                name: "User.userKey"
            },
            {
                label: "Member.userKey:",
                name: "Member.userKey"
            },
            {
                label: "Team Read:",
                name: "Team[].teamKey",
                type: "checkbox"
            }
        ]
    } );

    editor.on( 'onInitEdit', function () {
        editor.hide('User.userKey');
        editor.hide('Member.userKey');
    } );
 
    $('#Member').DataTable( {
        dom: "Tfrtip",
        order: [[ 0, "asc" ]],
        ajax: "table.User.php",
        columns: [
            { data: "User.name" },
            { data: "Member.label" },
            { data: "Member.description" },
            { data: "User.email" },
            { data: "Discipline.icon",
              mRender: function ( data ) {
                return '<img src="../disciplineManagement/'+data+'" width="30" height="30"></>';
              }
            },
            { data: "Member.teamKey"},
            { data: "Member.teamleader",
              mRender: function( data ) {
                if (data === '1') {
                    return 'Yes';
                } else {
                    return 'No';
                }
              }
            },
            { data: "Team", render: "[, ].name" }
        ],
        tableTools: {
            sRowSelect: "os",
            sSwfPath: "../DataTables-1.10.2/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
            aButtons: [
               // { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor },
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        },
        initComplete: function ( settings, json ) {
            editor.field( 'Member.teamKey' ).update( json.Team );
            editor.field( 'Member.symbol' ).update( json.Discipline );
            editor.field( 'Team[].teamKey' ).update( json.Team );
        }
    } );
} );

<?php
    
include( "../DataTables-1.10.2/extensions/Editor-1.3.2/php/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

$data = Editor::inst( $db, 'Member' )
    ->pkey( 'memberKey' )
    ->field(
        Field::inst( 'User.name' ),
        Field::inst( 'Member.label' ),
        Field::inst( 'Member.description' ),
        Field::inst( 'User.email' ),
        Field::inst( 'Member.symbol' ),
        Field::inst( 'Member.teamKey' ),
        Field::inst( 'Member.teamleader' ),
        Field::inst( 'Team.name' ),
        Field::inst( 'Discipline.icon' ),
        Field::inst( 'User.userKey' ),
        Field::inst( 'Member.userKey' )
    )
    ->leftJoin( 'Team', 'Team.teamKey', '=', 'Member.teamKey')
    ->leftJoin( 'User', 'User.userKey', '=', 'Member.userKey')
    ->leftJoin( 'Discipline', 'Discipline.name', '=', 'Member.symbol')
    ->join(
        Join::inst( 'Team', 'array' )
            ->join(
                array( 'memberKey', 'memberKey' ),
                array( 'teamKey', 'teamKey' ),
                'TeamLink'
            )
            ->fields(
                Field::inst( 'teamKey' ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->data(); 

if ( ! isset($_POST['action']) ) {
    // Get a list of types for the `select` list
    $data['Team'] = $db
        ->selectDistinct( 'Team', 'teamKey as value, name as label' )
        ->fetchAll();
        
    $data['Discipline'] = $db
        ->query( 'select', 'Discipline' )
                ->get( 'name as value, name as label' )
                ->where( 'name','%_alert%','NOT LIKE' )
                ->exec()
                ->fetchAll();
}
 
echo json_encode( $data );

On line 84 of the js script i have now { data: "Member.teamKey"}, This should be { data: "Team.name"}, but because of the use of the join array where i also use the Team table this is not working anymore.

I hope i explained it correctly and the problem can be fixed. I don't see it anymore....

allan

An update for anyone else who might be reading through this thread as we took the discussion offline to help resolve the issue.

Basically the problem was that Team was required as both a leftJoin and an array join (due to the structure of the database schema). So the ability to alias a table name was useful here:

->leftJoin( 'Team', 'Team.teamKey', '=', 'Member.teamKey')

becomes:

->leftJoin( 'Team as T', 'T.teamKey', '=', 'Member.teamKey')

and the Team.name field updated to be T.name. Everything else can stay as it was.

Regards,
Allan