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

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

nklinkersnklinkers Posts: 24Questions: 4Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0
    $(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
    
  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0
    <?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();
    
    ?>
    
  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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();
    }
    ?>
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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.

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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.

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0
    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.

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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!

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

    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.

  • nklinkersnklinkers Posts: 24Questions: 4Answers: 0

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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

This discussion has been closed.