Messes up tables after editing

Olan

Still truggeling with this tabel....
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. Team table should be untouched.

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

You have the table Team as both a left join and an array join. I think all bets are off as to what will happen in that situation (I really don't know what would happen and I'm wondering if I should add some error detection for it!).

Team table should be untouched.

I think if you remove the left join on Team that should be the case from looking at the code above (although I haven't run it of course, so am only 80% confident!). Let me know how you get on with it.

Allan

Olan

Hello Allan,

after removing the leftjoin Team (and also the field list item Team.name) the above code is working. but... :-)
I also need to display the Team name instead of the teamKey (line 84 in the js file). That's why I had also the leftjoin in it (join the Member.teamKey to Team.teamKey) to get the Team.name

Olan

Allan,

Any suggestions how to do this?

Thanks!

allan

Hi,

Sorry for the delay in replying - it had slipped off the front page!

So for the team name, you actually have that information already in the array returned, which should look something like:

Team: [
  { "teamKey": 1, "name": "Name 1" },
  { "teamKey": 2, "name": "Name 2" },
  { "teamKey": 3, "name": "Name 3" },
  ...
]

Is that about right?

In DataTables you can display the names using the array join syntax of the columns.data property. So it might look like:

{ data: "Team[, ].name" }

(that is in the columns array definition btw!).

That is saying, take the array Team, and join all inner name properties using the separator comma space (you can use whatever you want).

So with that done - the edit probably still won't be working I guess, but I'm not sure yet as to why that would be. Do you get any errors?

I'm wondering if it might be easiest for me to try and reproduce the error locally. For that, would you be able to send me a dump of your SQL schema and data, the Javascript (and I've got the PHP from above)? E-mail address is allan @ this-domain.net.

Thanks,
Allan