Parent / child editing with Editor Blog Example
Parent / child editing with Editor Blog Example
cpshart
Posts: 246Questions: 49Answers: 5
I have replicated your Blog example on my system as defined on your blog post (https://datatables.net/blog/2016-03-25 )
I need to be able to join tables to retrieve other information into the parent datatable, so in your replicated example I have added another table country (columns, id, name)
I have included the original working scripts below and also changes in an attempt to add country data and the results, any help in fixing this issue would be much appreciated. Thanks Colin
Table country (new table)
id, name
Table sites (addition country_id)
id, name, country_id
Table users (no change)
id, first_name, last_name, phone, site
the following system works, so selecting the parent row, results in output of corresponding child rows, as per your blog post
additional code to add country name to datatable results in errors and the child rows are no longer output
sites.php, sectional changes
before
...
Editor::inst( $db, 'sites' )
->fields(
Field::inst( 'id' )->set( false ),
Field::inst( 'sites.name' )->validator( 'Validate::notEmpty' )
)
...
after
...
Editor::inst( $db, 'sites' )
->fields(
Field::inst( 'sites.id' )->set( false ),
Field::inst( 'sites.name' )->validator( 'Validate::notEmpty' ),
Field::inst( 'country.name' )
)
...
->leftJoin( 'country', 'country.id', '=', 'sites.country_id' )
Client File
before
...
<thead>
<tr>
<th>Name</th>
<th>Users</th>
</tr>
</thead>
after
...
<thead>
<tr>
<th>Name</th>
<th>Users</th>
<th>Country</th>
</tr>
</thead>
before
...
foreach ($rows as $row ){
echo "<tr>";
echo "<td>$row->site</td>";
echo "<td>$row->users</td>";
echo "</tr>";
}
...
after
before
...
foreach ($rows as $row ){
echo "<tr>";
echo "<td>$row->site</td>";
echo "<td>$row->users</td>";
echo "<td>$row->country</td>";
echo "</tr>";
}
...
before
...
columns: [
{ data: 'name' },
{ data: 'users', render: function ( data ) {
return data.length;
} }
],
...
after
columns: [
{ data: 'name' },
{ data: 'users', render: function ( data ) {
return data.length;
} },
{ data: 'country' }
],
Errors
If I run the new client file (addition of country coding) with the old server files, selecting the parent row correctly displays the child rows, error is below
DataTables warning: table id=sites - Requested unknown parameter 'country' for row 0, column 2. For more information about this error, please see http://datatables.net/tn/4
and no country data is displayed in datatable
Invoking the new server file sites.php, results in error
DataTables warning: table id=sites - Requested unknown parameter 'name' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4
Name displays no row data
Country displays [object Object] in cell
Working Files (pre country change)
Client File
<head>
<title>Sites Users</title>
.... libraries here ...
</head>
<table id="sites" class="display">
<thead>
<tr>
<th>Name</th>
<th>Users</th>
</tr>
</thead>
<tbody>
<?php
global $wpdb;
global $current_user;
get_currentuserinfo();
$user_id = $current_user->ID;
$rows = $wpdb->get_results("
SELECT
sites.name AS site,
'1' AS users,
country.name AS country,
first_name AS firstname,
last_name AS lastname,
phone AS phone,
sites.name AS location
FROM
users
INNER JOIN sites ON (sites.id = users.site)
INNER JOIN country ON (country.id = sites.country_id)
");
foreach ($rows as $row ){
echo "<tr>";
echo "<td>$row->site</td>";
echo "<td>$row->users</td>";
echo "</tr>";
}
<?php
>
?>
... users section here all works ok ...
<input type='hidden' id='passuserid' value='<?php echo $current_user->ID; ?>'>
<script type="text/javascript">
(function($) {
var editor; // use a global for the submit and return data rendering in the examples
$(document).ready(function() {
var siteEditor = new $.fn.dataTable.Editor( {
ajax: "../../Editor-PHP-1.9.0/controllers/sites.php",
table: "#sites",
fields: [ {
label: "Site name:",
name: "name"
}
]
} );
window.editor = siteEditor; // for demo only!
var siteTable = $('#sites').DataTable( {
dom: "Bfrtip",
ajax: "../../Editor-PHP-1.9.0/controllers/sites.php",
columns: [
{ data: 'name' },
{ data: 'users', render: function ( data ) {
return data.length;
} }
],
select: {
style: 'single'
},
buttons: [
{ extend: "create", editor: siteEditor },
{ extend: "edit", editor: siteEditor },
{ extend: "remove", editor: siteEditor }
]
} );
var usersEditor = new $.fn.dataTable.Editor( {
ajax: {
url: '../../Editor-PHP-1.9.0/controllers/users.php',
data: function ( d ) {
var selected = siteTable.row( { selected: true } );
if ( selected.any() ) {
d.site = selected.data().id;
}
}
},
table: '#users',
fields: [ {
label: "First name:",
name: "users.first_name"
}, {
label: "Last name:",
name: "users.last_name"
}, {
label: "Phone #:",
name: "users.phone"
}, {
label: "Site:",
name: "users.site",
type: "select",
placeholder: "Select a location"
}
]
} );
var usersTable = $('#users').DataTable( {
dom: 'Bfrtip',
ajax: {
url: '../../Editor-PHP-1.9.0/controllers/users.php',
type: 'post',
data: function ( d ) { /* this code displays child rows matching id of selected parent row */
var selected = siteTable.row( { selected: true } );
if ( selected.any() ) {
d.site = selected.data().id;
}
}
},
columns: [
{ data: 'users.first_name' },
{ data: 'users.last_name' },
{ data: 'users.phone' },
{ data: 'sites.name' }
],
select: true,
buttons: [
{ extend: 'create', editor: usersEditor },
{ extend: 'edit', editor: usersEditor },
{ extend: 'remove', editor: usersEditor }
]
} );
/* This writes the users data to the sites JSON when Parent Row is Selected */
siteTable.on( 'select', function (e) {
usersTable.ajax.reload();
usersEditor
.field( 'users.site' )
.def( siteTable.row( { selected: true } ).data().id );
} );
/* This writes the users data to the sites JSON when Parent Row is Selected */
siteTable.on( 'deselect', function () {
usersTable.ajax.reload();
} );
usersEditor.on( 'submitSuccess', function () {
siteTable.ajax.reload();
} );
siteEditor.on( 'submitSuccess', function () {
usersTable.ajax.reload();
} );
} );
}(jQuery));</script>
sites.php
// DataTables PHP library
include( "../lib/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,
DataTables\Editor\ValidateOptions;
Editor::inst( $db, 'sites' )
->fields(
Field::inst( 'id' )->set( false ),
Field::inst( 'name' )->validator( 'Validate::notEmpty' )
)
->join(
Mjoin::inst( 'users' )
->link( 'sites.id', 'users.site' )
->fields(
Field::inst( 'id' )
)
)
->process( $_POST )
->json();
<?php
>
```
?>
users.php
<?php
// DataTables PHP library
include( "../lib/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,
DataTables\Editor\ValidateOptions;
if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
echo json_encode( [ "data" => [] ] );
}
else {
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'] )
->process($_POST)
->json();
}
Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
This question has an accepted answers - jump to answer
Answers
In the "after" state, could you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is please/
Thanks,
Allan
Hi Allan
Thanks for coming back to me, I have made changes according to the "after" state and the effect is that the parent datatable is populated as expected, but on selecting a parent row no child rows are displayed, whereas previous to the after change I could replicate your blog behaviour on selection of a parent row.
I have run debug as suggested and I will update my libraries as recommended by the debug results (I have not been developing for sometime, so I have not applied the recent updates)
Any ideas why the selection is now failing on adding a very simple joined table column expression would be much appreciated, as unfortunately this is an essential requirement of my site.
Here are the results of the debug below
Many Thanks Colin
Hi Allan
I have sent you a pm with access to my system to look into the problem, with thanks Colin
Hi
If anyone offer any suggestions on how I can add a joined table value to the parent table without disabling the ability to select a parent row and display the corresponding child rows.
The Parent Child rows editor only works if I reference values from the same table in the parent datatable.
I am very keen to get this working so I can apply the working example to my site.
Many Thanks Colin
Thanks for the details - I've replied to your PM.
Allan
Thanks, Allan I have sent you a PM.
I have solved the problem of being able to display the site and country name from another table whilst maintaining the selection of parent row and display associated child rows, changes to the scripts are shown below
Client Script
the reason the selection failed on inclusion of another table.column was this line needed to incorporate the tablename sites in sites.id
Server file sites.php extract
server file changes to sites.php
were
adding tablename to prefix columns in Editor Fields
```
Thanks for assistance
Colin
Unfortunately the previous post is not a complete solution, as the changes outlined above although fixing the display of joined table data compromise the behaviour of the editor of the child table, so I will continue to search for a solution and post when done.
Allan I have sent you a PM
Thanks Colin