Parent child issue

carrarachristophe · August 2023

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.

kthorngren · August 2023

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.

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.

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:

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

HTH,
Kevin

carrarachristophe · August 2023

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.

kthorngren · 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

carrarachristophe · August 2023

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();

kthorngren · August 2023

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

carrarachristophe · August 2023

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...

kthorngren · August 2023

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

carrarachristophe · August 2023

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.

kthorngren · August 2023

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

carrarachristophe · August 2023

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"".

kthorngren · August 2023

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

carrarachristophe · August 2023

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

kthorngren · August 2023

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

carrarachristophe · August 2023

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

kthorngren · 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

carrarachristophe · August 2023

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

kthorngren · August 2023

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

carrarachristophe · 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?

kthorngren · August 2023

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

carrarachristophe · August 2023

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

kthorngren · August 2023

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

carrarachristophe · August 2023

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.

Parent child issue

Parent child issue

Answers

Howdy, Stranger!

Categories

DataTables

Parent child issue

Parent child issue

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables