Parent child issue

Parent child issue

carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2
edited August 2023 in DataTables

Hello,

I am trying to implement the parent child as described in the example and the blog.
I need something simple, without editor buttons or the column count in the parent table.
Here is my code:

(function($){

$(document).ready(function() {

    const iso20022_pain001_GrpHdreditor = new DataTable.Editor({
        ajax: '../php/table.iso20022_pain001_GrpHdr.php',
        table: '#iso20022_pain001_GrpHdr'
    });

    const iso20022_pain001_GrpHdrtable = $('#iso20022_pain001_GrpHdr').DataTable( {
        ajax: 'php/table.iso20022_pain001_GrpHdr.php',
        columns: [
            {data: "iso20022_pain001_GrpHdr.MsgId_id"},
            {data: "iso20022_pain001_GrpHdr.InitgPty_Nm"},
            {data: "iso20022_pain001_GrpHdr.NbOfTxs"},
            {data: "iso20022_pain001_GrpHdr.CtrlSum"}
        ],
        select: {
            style: 'single'
        }
    } );

    const iso20022_pain001_PmtInfeditor = new DataTable.Editor({
        ajax: {
            url: '../php/table.iso20022_pain001_PmtInf.php',
            data: function (d) {
                var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                if (selected.any()) {
                    d.MsgId = selected.data().id;
                }
            }
        },
        table: '#iso20022_pain001_PmtInf'
    });
    
    const iso20022_pain001_PmtInftable = $('#iso20022_pain001_PmtInf').DataTable( {
        ajax: {
            url: '../php/table.iso20022_pain001_PmtInf.php',
            type: 'post',
            data: function (d) {
                var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                if (selected.any()) {
                    d.MsgId = selected.data().id;
                }
            }
        },
        columns: [
            {data: "iso20022_pain001_PmtInf.MsgId"},
            {data: "iso20022_pain001_PmtInf.PmtInf_id"},
            {data: "iso20022_pain001_PmtInf.NbOfTxs"},
            {data: "iso20022_pain001_PmtInf.ReqdExctnDt"}
        ]
        } );
        
    iso20022_pain001_GrpHdrtable.on('select', function (e) {
        iso20022_pain001_PmtInftable.ajax.reload();
     
        iso20022_pain001_PmtInfeditor.field('iso20022_pain001_PmtInf.MsgId').def(iso20022_pain001_GrpHdrtable.row({ selected: true }).data().id);
    });
     
    iso20022_pain001_GrpHdrtable.on('deselect', function () {
        iso20022_pain001_PmtInftable.ajax.reload();
    });
     
    iso20022_pain001_PmtInfeditor.on('submitSuccess', function () {
        iso20022_pain001_GrpHdrtable.ajax.reload();
    });
     
    iso20022_pain001_GrpHdreditor.on('submitSuccess', function () {
        iso20022_pain001_PmtInftable.ajax.reload();
    });

} );

}(jQuery));

But it is failing:
1. the child table data is displayed even without selecting a row from the parent table
2. when selecting a row from the parent table, I am getting the following message: "Uncaught Error: Unknown field name - iso20022_pain001_PmtInf.MsgId" related to line 58 above.

Any idea of where it is coming from?
In the example, what does "site" refer to in:

        data: function (d) {
            var selected = siteTable.row({ selected: true });
            if (selected.any()) {
                d.site = selected.data().id;
            }
        }

I replaced it by the column name of the child table on which I have a foreign key with the parent one, but I am not sure.

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    without editor buttons or the column count

    Are you saying you aren't using Editor functionality for either table? If you aren't using the Editor then remove that part of the code.

    Not sure what you mean by column count.

    1. the child table data is displayed even without selecting a row from the parent table

    Is your server script setup to look for the MsgId parameter being sent? This is used to filter the child table rows. If there are no parameters sent, which will be the case with no selected rows, then the server script should respond with no rows. Otherwise use the MsgId parameter to get the ID needed for the child data.

    1. when selecting a row from the parent table, I am getting the following message: "Uncaught Error: Unknown field name - iso20022_pain001_PmtInf.MsgId" related to line 58 above.

    Likely due to not defining any Editor fields.

    In the example, what does "site" refer to in:

    site is the parameter sent to the server to define the child data to send. You can use any parameter name that makes sense to your solution.

    Maybe it would be helpful to look at the ajax request and responses from the example you linked to. Open the browser's network inspector and select XHR filter. These steps are based on using Chrome:

    1. Select users.php and you will see there are no parameters sent to the server. The JSON response is {"data":[]} for no rows.
    2. Select a row in the parent then select the next users.php request.
    3. Click the payload tab and you will see the site parameter sent. The server script fetches all the child rows that match the parameter.
    4. In the response tab you will see all the rows for the site.

    HTH,
    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Hi Kevin,
    Thank you for your prompt answer.

    I removed all the Editor-linked code (I will add it again later) and have no more the Unknown field error message.

    I was indeed missing the Editor->where() method as mentioned here.
    The problem is that I again don't understand where the site is coming from in $_POST['site'] of the example.
    I am getting a Undefined array key error message.

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited August 2023

    The problem is that I again don't understand where the site is coming from in $_POST['site'] of the example.

    The example is using this:

        ajax: {
            url: '../php/users.php',
            type: 'post',
            data: function ( d ) {
                var selected = siteTable.row( { selected: true } );
     
                if ( selected.any() ) {
                    d.site = selected.data().id;
                }
            }
        },
    

    It is creating the site parameter in line 8 with d.site. In your code you have d.MsgId = selected.data().id;. So you will want to use $_POST['MsgId'] instead. Also the example has an id object which isn't displayed in the table. You might need to change the .id in line 8 to match the unique id you have in the parent data.

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Thank you Kevin, but still getting the same Undefined array key "MsgId" message.
    I must be missing something.

    $(document).ready(function() {
        const iso20022_pain001_GrpHdrtable = $('#iso20022_pain001_GrpHdr').DataTable( {
            ajax: 'php/table.iso20022_pain001_GrpHdr.php',
            columns: [
                {data: "iso20022_pain001_GrpHdr.MsgId_id"},
                {data: "iso20022_pain001_GrpHdr.InitgPty_Nm"},
                {data: "iso20022_pain001_GrpHdr.NbOfTxs"},
                {data: "iso20022_pain001_GrpHdr.CtrlSum"}
            ],
            select: {
                style: 'single'
            }
        } );
    
        const iso20022_pain001_PmtInftable = $('#iso20022_pain001_PmtInf').DataTable( {
            ajax: {
                url: '../php/table.iso20022_pain001_PmtInf.php',
                type: 'post',
                data: function (d) {
                    var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                    if (selected.any()) {
                        d.MsgId = selected.data().MsgId_id;
                    }
                }
            },
            columns: [
                {data: "iso20022_pain001_PmtInf.MsgId"},
                {data: "iso20022_pain001_PmtInf.PmtInf_id"},
                {data: "iso20022_pain001_PmtInf.NbOfTxs"},
                {data: "iso20022_pain001_PmtInf.ReqdExctnDt"}
            ]
        } );
            
        iso20022_pain001_GrpHdrtable.on('select', function (e) {
            iso20022_pain001_PmtInftable.ajax.reload();
        });
         
        iso20022_pain001_GrpHdrtable.on('deselect', function () {
            iso20022_pain001_PmtInftable.ajax.reload();
        });
    } );
    
    

    Server side for child table, with error on line 10:

    Editor::inst( $db, 'iso20022_pain001_PmtInf ', 'PmtInf_id' )
        ->fields(
            Field::inst( 'iso20022_pain001_PmtInf.PmtInf_id' ),
            Field::inst( 'iso20022_pain001_PmtInf.MsgId' ),
            Field::inst( 'iso20022_pain001_PmtInf.NbOfTxs' ),
            Field::inst( 'iso20022_pain001_PmtInf.ReqdExctnDt' ),
            Field::inst( 'iso20022_pain001_GrpHdr.MsgId_id' )
        )
        ->leftJoin( 'iso20022_pain001_GrpHdr', 'iso20022_pain001_GrpHdr.MsgId_id', '=', 'iso20022_pain001_PmtInf.MsgId' )
        ->where( 'iso20022_pain001_PmtInf.MsgId', $_POST['MsgId'] )
        ->process( $_POST )
        ->json();
    

    Server side for parent table:

    Editor::inst( $db, 'iso20022_pain001_GrpHdr ', 'MsgId_id' )
        ->fields(
            Field::inst( 'iso20022_pain001_GrpHdr.MsgId_id' ),
            Field::inst( 'iso20022_pain001_GrpHdr.InitgPty_Nm' ),
            Field::inst( 'iso20022_pain001_GrpHdr.CtrlSum' ),
            Field::inst( 'iso20022_pain001_GrpHdr.CreDtTm' ),
            Field::inst( 'iso20022_pain001_GrpHdr.NbOfTxs' )
        )
        ->process( $_POST )
        ->json();
    
  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    The parameter will exist only when a parent row is selected. Take a look at the server script in the Child table section. Specifically this:

    if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
        echo json_encode( [ "data" => [] ] );
    }
    

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Thanks Kevin,
    it solved the fact that the child row data was populated without selecting a row in the parent table.
    But i now get an "Uncaught Error: Unknown field name - iso20022_pain001_PmtInf.MsgId" error message...

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    Are you using the Editor or not? I thought you removed the Editor code.

    Uncaught Error: Unknown field name - iso20022_pain001_PmtInf.MsgId

    This suggests that an Editor field for iso20022_pain001_PmtInf.MsgId has not been defined.

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Thanks Kevin. No more error message now, so getting close.
    But no query triggered when selecting a row from the parent table, hence no data displayed in the child table.
    I will review the code and compare with the example, but I am pretty sure it comes from the site part, which still sounds ambiguous to me.

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    I would start by making sure the select event for the parent is fired. Then use the browser's network inspector to see the ajax request and response. Make sure the request has the correct MsgId value.

    My guess is that you don't have a id object in your parent row data so this code isn't setting the MsgId parameter correctly

                data: function (d) {
                    var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                    if (selected.any()) {
                        d.MsgId = selected.data().MsgId_id;
                    }
                }
    

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Hi Kevin,

    My guess is that you don't have a id object in your parent row data


    What do you mean exactly? I am not sure what part of the code you are refering to.
    * The primary key of the parent table is: iso20022_pain001_GrpHdr.MsgId_id
    * The primary key of the child table is: iso20022_pain001_PmtInf.PmtInf_id
    * The child table field on which I have the fk is: iso20022_pain001_PmtInf.MsgId

    So my where statement must be:
    iso20022_pain001_PmtInf.MsgId = iso20022_pain001_GrpHdr.MsgId_id
    Which I converted into:

    ->where( 'iso20022_pain001_PmtInf.MsgId', $_POST['MsgId_id'] )
    

    I know that there is something wrong between:

    $_POST['MsgId_id']
    

    and (equivalent of that)

        const iso20022_pain001_PmtInftable = $('#iso20022_pain001_PmtInf').DataTable( {
            ajax: {
                url: '../php/table.iso20022_pain001_PmtInf.php',
                type: 'post',
                data: function (d) {
                    var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                    if (selected.any()) {
                        d.MsgId = selected.data().MsgId_id;
                    }
    

    because when I remove the if statement (so the request on the child table is made) I get "Undefined array key "MsgId_id"".

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    You have defined the columns like this:

            columns: [
                {data: "iso20022_pain001_PmtInf.MsgId"},
                {data: "iso20022_pain001_PmtInf.PmtInf_id"},
                {data: "iso20022_pain001_PmtInf.NbOfTxs"},
                {data: "iso20022_pain001_PmtInf.ReqdExctnDt"}
            ]
    

    You will need use this instead:

                if (selected.any()) {
                    d.MsgId = selected.data().iso20022_pain001_PmtInf.MsgId;
                }
    

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Now getting: Uncaught TypeError: selected.data().iso20022_pain001_PmtInf is undefined

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    because when I remove the if statement

    Did you put the if statement back? The error is likely due to not having any rows selected.

    "Undefined array key "MsgId_id""

    You have this:

    d.MsgId = selected.data().iso20022_pain001_PmtInf.MsgId;
    

    But are trying to get the parameter with $_POST['MsgId_id']. Use $_POST['MsgId'] so it matches the parameter name you are sending.

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Hi Kevin,
    I sent you a private message so you can have a look directly to the code.

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited August 2023

    Your parent table has this config:

            columns: [
                {data: "iso20022_pain001_GrpHdr.MsgId_id"},
                {data: "iso20022_pain001_GrpHdr.InitgPty_Nm"},
                {data: "iso20022_pain001_GrpHdr.NbOfTxs"},
                {data: "iso20022_pain001_GrpHdr.CtrlSum"}
            ],
    

    Change this:

    d.MsgId = selected.data().iso20022_pain001_PmtInf.MsgId;
    

    To this:

    d.MsgId = selected.data().iso20022_pain001_PmtInf.MsgId_id;
    

    The reference to iso20022_pain001_PmtInf.MsgId is not found so the Ajax request silently fails and is not sent.

    Use the browser's debugger to easily see this error, for example:

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    iso20022_pain001_PmtInf.MsgId_id does not exist in the database, so it fails.

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    Answer ✓

    Your names are confusing me :-). You want the ID from the parent table, like this:

    d.MsgId = selected.data().iso20022_pain001_GrpHdr.MsgId_id;
    

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2
    edited August 2023

    Then: Uncaught TypeError: selected.data().iso20022_pain001_GrpHdrtable is undefined.

    I think I tried every single possible combination.
    Are we sure the code mentioned in the blog is correct?

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    Its because you have this:

    selected.data().iso20022_pain001_GrpHdrtable.MsgId_id
    

    But it should be this:

    selected.data().iso20022_pain001_GrpHdr.MsgId_id
    

    So yes I believe the blog is correct.

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    My apologies.
    Undefined array key "MsgId_id" in /home/carrarm/test/SpryMedia/table.iso20022_pain001_PmtInf.php on line 32

    We are back to $_POST['MsgId_id']

    I don't understand why that part in the blog

    d.site = selected.data().id;
    

    becomes

    d.MsgId = selected.data().iso20022_pain001_GrpHdr.MsgId_id;
    

    and why that part in the blog

    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->where( 'site', $_POST['site'] )
    

    becomes

    ->leftJoin( 'iso20022_pain001_GrpHdr', 'iso20022_pain001_GrpHdr.MsgId_id', '=', 'iso20022_pain001_PmtInf.MsgId' )
    ->where( 'iso20022_pain001_PmtInf.MsgId', $_POST['MsgId_id'] )
    

    it looks like we are complicating

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    The idea of that code is the unique ID in the parent can be used to fetch the child data.

    d.site = selected.data().id;

    This is the parent's unique ID to the script in the parameter site.

    Look at the full example code:

        Editor::inst( $db, 'users' )
            ->field(
                Field::inst( 'users.first_name' ),
                Field::inst( 'users.last_name' ),
                Field::inst( 'users.phone' ),
                Field::inst( 'users.site' )
                    ->options( 'sites', 'id', 'name' )
                    ->validator( 'Validate::dbValues' ),
                Field::inst( 'sites.name' )
            )
            ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
            ->where( 'site', $_POST['site'] )
    

    The child table is defined to display fields from both the child table and from the parent using sites.name. The leftJoin is used to fetch the sites.name. And the where is used to filter the data to those matching the parent ID.

    If you don't want to display any of the parent data in the child then you should be able to remove the leftJoin.

    Kevin

  • carrarachristophecarrarachristophe Posts: 100Questions: 24Answers: 2

    Hi Kevin,
    I started from scratch to make sure I was not missing anything.
    Here is the code that works. I hope that I did not make any mistake and it can help.

    Editor::inst( $db, 'parent', 'parent_id' )
        ->fields(
            Field::inst( 'parent.parent_id' ),
            Field::inst( 'parent.name' )
        )
        ->process( $_POST )
        ->json();
    
    if ( ! isset($_POST['parent_id']) || ! is_numeric($_POST['parent_id']) ) {
        echo json_encode( [ "data" => [] ] );
    }
    else {
    Editor::inst( $db, 'child', 'child_id' )
            ->fields(
                Field::inst( 'child.child_id' ),
                Field::inst( 'child.name' ),
                Field::inst( 'child.parent_id' )
                Field::inst( 'parent.parent_id' )
            )
            ->leftJoin( 'parent', 'parent.parent_id', '=', 'child.parent_id' )
            ->where( 'child.parent_id', $_POST['parent_id'] )
            ->process( $_POST )
            ->json();
    }
    
    $(document).ready(function() {
        const parenttable = $('#parent').DataTable( {
            ajax: 'table.parent.php',
            columns: [
                {data: "parent.parent_id"},
                {data: "parent.name"}
            ],
            order: [[ 0, 'asc' ]],
            select: {
                style: 'single'
            }
        });
    
        const childtable = $('#child').DataTable( {
            ajax: {
                url: 'table.child.php',
                type: 'post',
                data: function (d) {
                    var selected = parenttable.row({ selected: true });
                    if (selected.any()) {
                        d.parent_id = selected.data().parent.parent_id;
                    }
                }
            },
            columns: [
                {data: "child.parent_id"},
                {data: "child.child_id"},
                {data: "child.name"}
            ],
            order: [[ 0, 'asc' ], [ 1, 'asc' ]],
            select: {
                style: 'single'
            }
        } );
    
        parenttable.on('select', function (e) {
            childtable.ajax.reload();
        });
        
        parenttable.on('deselect', function () {
            childtable.ajax.reload();
        });
    
    } );
    

    Which gives in my case:

            ->where( 'iso20022_pain001_PmtInf.MsgId', $_POST['MsgId_id'] )
    

    And as you recommended above:

        const iso20022_pain001_PmtInftable = $('#iso20022_pain001_PmtInf').DataTable( {
            ajax: {
                url: 'table.iso20022_pain001_PmtInf.php',
                type: 'post',
                data: function (d) {
                    var selected = iso20022_pain001_GrpHdrtable.row({ selected: true });
                    if (selected.any()) {
                        d.MsgId = selected.data().iso20022_pain001_GrpHdr.MsgId_id;
                    }
                }
            },
    

    Part of the problem was as well in the if statement of the child.php file

    Thank you very much for your help.

Sign In or Register to comment.