Parent/Child where child table has a joined table. How to get this data?

nklinkers

Hello,

I have a page with parent/child like https://datatables.net/blog/2016-03-25, but I want in the child table a File upload table. So what I have is a parent table with a linked table and the child table is the mcfiles table. The thing I'm missing is how I get the "files" table joined to the whole thing, because the "files" table is joined to mcfiles.So I should have a mjoin with the linked table (mcfiles) and that should be linked to the files table. I hope I am a bit clear with my problem and hope someone can help me how to do this.

allan

So would there be one image per row in the child table? If so, the setup shown in the blog post should work with that I think - just use upload for the upload field in the child table.

Allan

nklinkers

Hello Allen,

I tried that already, but the problem is the POST field I have to use. I need the field mcf_id from de linked table in the post variable which I don't have because I get the id of the mcplaylist table, because that is the parent table.
So I have a mcplaylist table and the mcfiles and files tables, (these 2 are the file upload) and a linked table to join the mcplaylist table and the mcfiles table. Besides that there is the joined table files for the upload.

MCPLAYLIST
id name active startdate enddate lyid clgrid mandatory
1 juli 1 2017-07-01 00:00:00 2017-07-31 00:00:00 1 14 1
2 juni 0 2017-06-01 00:00:00 2017-06-30 00:00:00 1 14 1
3 aug 0 2018-08-01 00:00:00 2018-08-31 00:00:00 1 14 1

MCFILES
id image duration clId active ma di wo do vi za zo tijdvan tijdtot
12 18 10 14 0 1 1 1 1 1 0 0 10:13:00 19:14:00
13 19 10 14 0 1 0 1 0 1 0 0 12:38:00 16:38:00
14 20 10 14 0 1 1 0 0 0 0 1 11:16:00 19:16:00
15 21 10 14 0 1 1 0 0 0 0 0 17:00:00 23:04:00

FILES
imageid filename filesize web_path system_path
18 logo.png 7184 /uploads/logo.png C:/www/uploads/logo.png
19 rol.mp4 3080785 /uploads/rol.mp4 C:/www/uploads/rol.mp4
20 icon.png 562 /uploads/icon.png C:/www/uploads/icon.png
21 2017.png 193929 /uploads/2017.png C:/www/uploads/2017.png

MCPLAYLIST_MCFILES (LINKED TABLE)
id mcpl_id mcf_id
12 2 14
18 1 12
19 1 14
20 1 15
21 3 12
22 3 13
23 3 14
24 3 15

I hope you see my problem now?

Thanks in advance.

allan

I need the field mcf_id from de linked table in the post variable which I don't have because I get the id of the mcplaylist table, because that is the parent table.

It sounds like this is the problem we need to address. Can you show me the code you are using please?

Allan

nklinkers

$(document).ready(function() {
    var playlistEditor = new $.fn.dataTable.Editor( {
        ajax: "mcplaylist_mcfilesgen.php",
        processing: true,
        serverSide: true,
        table: "#playlist_files",
        fields: [ { label: "Aktief:",     name: "mcplaylist.active",      type: "radio", options: [ { label: "Aktief",  value: 1 }, { label: "Niet aktief", value: 0 } ], def: 0 }, 
    { label: "Naam:",       name: "mcplaylist.name" }, 
    { label: "Startdatum:", name: "mcplaylist.startdate",   type: "datetime",
        opts:  {
            showWeekNumber: true
        } },  
    { label: "Einddatum:",  name: "mcplaylist.enddate",     type: "datetime",
        opts:  {
            showWeekNumber: true
        } }
        ]
        }
    } );
 
    var playlistTable = $('#playlist_files').DataTable( {
        dom: "Bfrtip",
        serverSide: true,
        ajax: {
            url: "mcplaylist_mcfilesgen.php",
            type: 'POST'
        },
        language: {
            "url": "datatables/i18n/dutch.json"
        },
        columns: [
        { data: "mcplaylist.active", class: "center",
                    render: function (val, type, row) { return val == 0 ? "<img src='images/status0.png' width='16' height='16'>" : "<img src='images/status1.png' width='16' height='16'>"; } 
                },
            { data: "mcplaylist.name" },
            { data: "mcplaylist.startdate" },
            { data: "mcplaylist.enddate" },
        { data: 'mcfiles', render: function ( data ) { return data.length; } }
        ],
        select: 'single',
        order:[[0, 'desc'], [ 2, 'desc' ]],
        buttons: [
            { extend: "create", "text":'<span class="glyphicon btn-glyphicon glyphicon-plus text-success"></span> Nieuw',"className": 'btn btn-default btn-md',  editor: playlistEditor, 
                    formButtons: [ { label: 'Afbreken', fn: function () { this.close(); } }, 'Opslaan' ] },
            { extend: "edit", "text":'<span class="glyphicon btn-glyphicon glyphicon-pencil text-info"></span> Wijzig',"className": 'btn btn-default btn-md', editor: playlistEditor,
                    formButtons: [ { label: 'Afbreken', fn: function () { this.close(); } }, 'Opslaan' ] },
            { extend: "remove", "text":'<span class="glyphicon btn-glyphicon glyphicon-trash text-danger"></span> Verwijder',"className": 'btn btn-default btn-md', editor: playlistEditor,
                    formButtons: [ { label: 'Afbreken', fn: function () { this.close(); } }, 'Verwijder' ] }
        ]
    } );
    var fileseditor = new $.fn.dataTable.Editor( {
        ajax: { 
            url: "mcfilesgenj.php",
            data: function ( d ) {
            var selected = playlistTable.row( { selected: true } );
                 if ( selected.any() ) {
                    d.playlist = selected.data().mcplaylist.id;
                 }
            }
        },
        processing: true,
        serverSide: true,
        table: "#files",
        fields: [ {
                label: "Bestand:",
                name: "mcfiles.image",
                type: "upload",
                display: function ( id ) {
                    return '<img src="'+editor.file( 'files', id ).web_path+'"/>';
                }
            }, {
                label: "Looptijd (sec):",
                name: "mcfiles.duration",
                attr: { "type": "number" },
                def: '10'
            }, {
                label: "Klant:",
                name: "mcfiles.clId",
                type: "hidden"
            }, {
                label: "Maandag:",
                name: "mcfiles.maandag",
                type: "checkbox",
                separator: "|",
                options:   [
                    { label: '', value: 1 }
                    ]
            } //and the rest of the fields....
            }
        ]
    } );

    var filestable = $('#files').DataTable( {
        dom: "Bfrtip",
        serverSide: true,
        ajax: {
            url: "mcfilesgenj.php",
            type: 'POST',
            data: function ( d ) {
            var selected = playlistTable.row( { selected: true } );
             if ( selected.any() ) {
                d.playlist = selected.data().mcplaylist.id;
             }
            }
        },
        language: {
            "url": "datatables/i18n/dutch.json"
        },
    columns: [
            {
                data: "mcfiles.image",
                render: function ( id ) {
                    return id ?
                        '<img height=50 border=0 src="'+editor.file( 'files', id ).web_path+'"/>' :
                        null;
                },
                defaultContent: "Geen bestand",
                title: "Bestand"
            },
            { 
                data: 'mcfiles.image', 
                    render: function ( id ) {
                        return id ?
                            editor.file( 'files', id ).filename :
                            'Geen info';
                    } 
            },
            { data: 'mcfiles.image', 
                    render: function ( id ) {
                        return id ?
                            editor.file( 'files', id ).filesize :
                            'Geen info';
                    } 
            },
            { data: "mcfiles.duration" }
        ],
        select: true,
        aaSorting: [[0, 'asc']],
        buttons: [
            { extend: "create", "text":'<span class="glyphicon btn-glyphicon glyphicon-plus text-success"></span> Nieuw',"className": 'btn btn-default btn-md',  editor: fileseditor,
                formButtons: [ {
                        label: 'Afbreken',
                        fn: function () { this.close(); } }, 'Opslaan' ] },
            { extend: "edit", "text":'<span class="glyphicon btn-glyphicon glyphicon-pencil text-info"></span> Wijzig',"className": 'btn btn-default btn-md', editor: fileseditor,
                formButtons: [ {
                        label: 'Afbreken',
                        fn: function () { this.close(); } }, 'Opslaan' ] },
            { extend: "remove", "text":'<span class="glyphicon btn-glyphicon glyphicon-trash text-danger"></span> Verwijder',"className": 'btn btn-default btn-md', editor: fileseditor,
                formButtons: [ {
                        label: 'Afbreken',
                        fn: function () { this.close(); } }, 'Verwijder' ] }
        ]
    } );
    playlistEditor.on ( 'postEdit postCreate postRemove', function () { playlist_files.ajax.reload( null, false ); });
    playlistEditor.on('open', function (e, type) {
        var mode = playlistEditor.mode();
        if (mode === "create") { playlistEditor.hide('mcplaylist.active'); }
        if (mode === "edit") { playlistEditor.show('mcplaylist.active'); }
    });  

    playlistTable.on( 'select', function () {
        filestable.ajax.reload();
        fileseditor
    .field( 'id' ) //---------which id we need here?
        .def( playlistTable.row( { selected: true } ).data().id );
    } );
 
    playlistTable.on( 'deselect', function () {
        filestable.ajax.reload();
    } );  

    playlistEditor.on( 'submitSuccess', function () {
        filestable.ajax.reload();
    } );
 
    fileseditor.on( 'submitSuccess', function () {
        playlistTable.ajax.reload();
    } );
}); //document ready

nklinkers

```
<?php
session_start();
include("Classes/class.acl.php");
$myACL = new ACL();

include( "datatables/Editor-PHP-1.6.2/php/DataTables.php" );

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

$editor=Editor::inst( $db, 'mcplaylist', 'id' )
->field(
Field::inst( 'mcplaylist.id' )->set( false ),
Field::inst( 'mcplaylist.name' )->validator( 'Validate::notEmpty' ),
Field::inst( 'mcplaylist.active' ),
Field::inst( 'mcplaylist.startdate' )->validator( 'Validate::notEmpty' )
->validator( 'Validate::dateFormat', array( "format" => Format::DATE_ISO_8601, "message" => "Datum invullen in het formaat yyyy-mm-dd" ) )
->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
Field::inst( 'mcplaylist.enddate' )->validator( 'Validate::notEmpty' )
->validator( 'Validate::dateFormat', array( "format" => Format::DATE_ISO_8601, "message" => "Datum invullen in het formaat yyyy-mm-dd" ) )
->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
Field::inst( 'mcplaylist.lyid' )->options( Options::inst()->table( 'dslayout' )->value( 'lyId' )->label( 'lyName' ) )->validator( 'Validate::dbValues' ),
Field::inst( 'dslayout.lyId' ),
Field::inst( 'mcplaylist.clgrid' )->set( Field::SET_CREATE ),
Field::inst( 'mcplaylist.mandatory' )
)
->leftJoin( 'dslayout', 'dslayout.lyId', '=', 'mcplaylist.lyid' )
->join(
Mjoin::inst( 'mcfiles')
->link( 'mcplaylist.id', 'mcplaylist_mcfiles.mcpl_id' )
->link( 'mcfiles.id', 'mcplaylist_mcfiles.mcf_id' )
->order( 'image asc' )
->set(false)
->fields(
Field::inst( 'id as mcfiles_id')
->validator( 'Validate::required' )
->options( Options::inst()
->table( 'mcfiles' )
->value( 'id' )
->label( 'image' )
),
Field::inst( 'mcfiles.image as mcfiles_image' ),
Field::inst( 'mcfiles.duration as mcfiles_duration' ),
Field::inst( 'mcfiles.clId as mcfiles_clid' ),
Field::inst( 'mcfiles.maandag as mcfiles_maandag' ),
Field::inst( 'mcfiles.dinsdag as mcfiles_dinsdag' ),
Field::inst( 'mcfiles.woensdag as mcfiles_woensdag' ),
Field::inst( 'mcfiles.donderdag as mcfiles_donderdag' ),
Field::inst( 'mcfiles.vrijdag as mcfiles_vrijdag' ),
Field::inst( 'mcfiles.zaterdag as mcfiles_zaterdag' ),
Field::inst( 'mcfiles.zondag as mcfiles_zondag' ),
Field::inst( 'mcfiles.tijdvan as mcfiles_tijdvan' ),
Field::inst( 'mcfiles.tijdtot as mcfiles_tijdtot' )
)
)
->on( 'postEdit', function ( $editor, $id, $values, $row ) {
// On Edit update all the other records to make only one record active
if($row['mcplaylist']['active']==1)
{
/* zet alle andere records op 0 van deze klant*/
$editor->db()
->query( 'update', 'mcplaylist' )
->set( 'active', '0', false )
->where( 'clgrid', $row['mcplaylist']['clgrid'], '=' )
->where( 'id', $row['mcplaylist']['id'], '<>' )
->exec();
}
});

//$aa=intval($_SESSION['SESS_GROUP']);
//$editor->on( 'preCreate', function ( $editor, $values ) use ( $aa ) {
//$editor->field( 'mcplaylist.clgrid' )->setValue( $aa ); } );

//if ( ($myACL->hasPermission('access_admin')) || ($myACL->hasPermission('limited_admin')) ) {  //admins show all
//  }
//else {
    //$editor->where( function ( $q ) use ( $aa ) { $q->where( 'mcplaylist.clgrid', $aa ); } );           
//};

$editor->process( $_POST )->json();

<?php > ``` ?>

nklinkers

Previous file was mcplaylist_mcfilesgen.php
This is de mcfilesgenj.php:
```
<?php
session_start();
include("Classes/class.acl.php");
$myACL = new ACL();
// DataTables PHP library and database connection
include( "datatables/Editor-PHP-1.6.2/php/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;

if ( ! isset($_POST['playlist']) || ! is_numeric($_POST['playlist']) ) {
echo json_encode( [ "data" => [''] ] );
}
else {
Editor::inst( $db, 'mcfiles', 'id' )
->field(
Field::inst( 'mcfiles.image' )
->upload(
Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/uploads/NAME' )
->db( 'files', 'id', array(
'filename' => Upload::DB_FILE_NAME,
'filesize' => Upload::DB_FILE_SIZE,
'web_path' => Upload::DB_WEB_PATH,
'system_path' => Upload::DB_SYSTEM_PATH
))
->validator( function ( $file ) {
return$file['size'] >= 2000000000 ?
"Files must be smaller than 2GB" :
null;
} )
->allowedExtensions( array( 'png', 'jpg', 'gif', 'mov', 'mp4' ), "De volgende extensies zijn toegestaan: png, jpg, gif, mov en mp4!" )
),
Field::inst( 'mcfiles.duration' )->validator( 'Validate::notEmpty' ),
Field::inst( 'mcfiles.clId' )->set( Field::SET_CREATE ),
Field::inst( 'mcfiles.maandag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.dinsdag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.woensdag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.donderdag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.vrijdag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.zaterdag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.zondag' )->setFormatter( function ( $val, $data, $opts ) { return ! $val ? 0 : 1; } ),
Field::inst( 'mcfiles.tijdvan' ),
Field::inst( 'mcfiles.tijdtot' )
)
->join(
Mjoin::inst( 'mcplaylist_mcfiles')
->link( 'mcplaylist_mcfiles.mcf_id', 'mcfiles.id' )
->link( 'mcplaylist_mcfiles.mcpl_id', 'mcplaylist.id' )
->order( 'id asc' )
->set(false)
->fields(
Field::inst( 'id')
->validator( 'Validate::required' )
->options( Options::inst()
->table( 'mcplaylist_mcfiles' )
->value( 'id' )
),
Field::inst( 'mcplaylist_mcfiles.mcpl_id' ),
Field::inst( 'mcplaylist_mcfiles.mcf_id' )
)
)

//$aa=intval($_SESSION['SESS_GROUP']); //GROUP ID in plaats CLIENT ID !!!!!
//$editor->on( 'preCreate', function ( $editor, $values ) use ( $aa ) {
 //   $editor
 //       ->field( 'mcfiles.clId' )
 //       ->setValue( $aa );
//} );


//if ( ($myACL->hasPermission('access_admin')) || ($myACL->hasPermission('limited_admin')) ) {  //admins show all
//    }
//else {
//->where( function ( $q ) use ( $aa ) {
//        $q->where( 'mcfiles.clId', $aa ); 
//    } )
//}

->where( 'mcplaylist_mcfiles.mcpl_id', $_POST['playlist'] )
->process( $_POST )
->json();

}

<?php > ``` ?>

allan

Thanks. In the files editor Ajax configuration you have:

        ajax: {
            url: "mcfilesgenj.php",
            data: function ( d ) {
            var selected = playlistTable.row( { selected: true } );
                 if ( selected.any() ) {
                    d.playlist = selected.data().mcplaylist.id;
                 }
            }
        },

Are you able to add the mcf_id field to the d object as well? Or have I misunderstood.

Allan

nklinkers

Hello Allan,

I tried adding d.mcfid = selected.data().mcplaylist_mcfiles.mcf_id;
but this doesn't work.

Besides that I get an error: DataTables warning: table id=files - Requested unknown parameter 'mcfiles.duration' for row 0, column 3, despite the field is defined in the mcfilesgenj.php page.

nklinkers

Hello Allan,

in my mcplaylist_mcfilesgen.php I now get all the data that I need in the json response. Can I use this file in de javascript for the child table too or does it need to be a different file? I only don't get it to use all the data yet. Have to figure out how I need to address the fields I need in the child table and how to use the "if" statement with the POST variable still.

nklinkers

data    […]
0   {…}
DT_RowId    row_1
mcplaylist  {…}
id  1
name    maand juli
active  1
startdate   2017-07-01
enddate 2017-07-31
lyid    1
clgrid  14
mandatory   1
dslayout    {…}
lyId    1
mcfiles […]
0   {…}
1   {…}
mcfiles_id  14
mcfiles_image   20
mcfiles_duration    10
mcfiles_clid    14
mcfiles_maandag 1
mcfiles_dinsdag 1
mcfiles_woensdag    0
mcfiles_donderdag   0
mcfiles_vrijdag 0
mcfiles_zaterdag    0
mcfiles_zondag  1
mcfiles_tijdvan 11:16:00
mcfiles_tijdtot 19:16:00
2   {…}
1   {…}
2   {…}
options {…}
mcplaylist.lyid […]
mcfiles[].mcfiles_id    […]
0   
1   
2   
label   20
value   14
3   
files   {…}
files   {…}
18  {…}
19  {…}
20  {…}
id  20
filename    asset-upload-icon.png
filesize    562
web_path    /uploads/asset-upload-icon.png
system_path C:/www/uploads/asset-upload-icon.png
21  {…}
draw    1
recordsTotal    3
recordsFiltered 3

Above the json response, but I don't know how to get the fields in the mcfiles table. F.e.: mcfiles_duration or mcfiles_image. I get an error stating Unknown field every time.

nklinkers

Hello Allan,

I managed to get it to run the way I want. Just trying and looking for errors everytime. The problem was the sql command was not the right one and some trying on different joins and an error in the js with editor.file instead of fileseditor.file maneged to solve the problem. Pffff at last. Thank you very much for thinking with me!

allan

When you add that line, does it correctly send the "mcfid" parameter, or when you say that it doesn't work do you mean that that variable is undefined?

Allan

nklinkers

After all I didn't need the mcf_id in the post, only the playlist_id. I managed to get the where clause to let it all happen: ->where( function ( $q ) {
$q->where( 'mcfiles.id', '(SELECT mcf_id FROM mcplaylist_mcfiles WHERE mcpl_id = :P_playlist )', 'IN', false );
$q->bind( ':P_playlist', $_POST['playlist']);

Only problem I still have is that it now shows the files that are connected to the playlist but when I add a file in the child table or delete a file from the child table, it adds the file to the media library but it is not connected to the playlist. and when I delete the file, it is deleted from the medialibrary, and that is not what it is soposed to be. It must be deleted only from the playlist. So the linked table should be updated and not the mcfiles table. I have to figure this out yet.

nklinkers

And when I select a playlist in the parent, I cannot edit or delete it. The buttons don't work anymore. Strange.

allan

Sorry - I completely lost track of this thread!

When the buttons don't work any more, has an error been reported either in the Javascript console of your browser, or on the server-side (usually in the Ajax response)?

Allan