parent child datatable editor
parent child datatable editor
mimi123456789
Posts: 15Questions: 1Answers: 0
Hello,
I'm working with the example "parent-child" (https://editor.datatables.net/examples/advanced/parentChild.html)
It's working until I try to add a field type "select". In order to get this field, I modify my files "table.test.js" and "sites.php".
Particularly, I add a "leftJoin" to have the text of my field "select".
How can I add this king of field without having a warning "DataTables warning: table id=sites - Table part of the field "id" was not found. In Editor instances that use a join, all fields must have the database table set explicitly.
The code I use is below.
My table.test.js file
$(document).ready(function() {
var siteEditor = new $.fn.dataTable.Editor( {
ajax: "./php/sites.php",
table: "#sites",
fields: [
{
label: "Batiment:",
name: "sites.batiment"
},
{
label: "Gisement:",
name: "sites.gisement"
},
{
label: "Confidentialité:",
name: "sites.o_habilitation"
},
{
label: "Description:",
name: "sites.description"
},
]
} );
var siteTable = $('#sites').DataTable( {
dom: "Bfrtip",
ajax: "./php/sites.php",
columns: [
{data: 'sites.batiment'},
{ data: 'sites.gisement' },
{ data: 'o_habilitation.libelle_court_hab' },
{ data: 'sites.description' },
{ 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: './php/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: "Site:",
name: "users.site",
type: "select",
placeholder: "Select a location"
}
]
} );
var usersTable = $('#users').DataTable( {
dom: 'Bfrtip',
ajax: {
url: './php/users.php',
type: 'post',
data: function ( d ) {
var selected = siteTable.row( { selected: true } );
if ( selected.any() ) {
d.site = selected.data().id;
}
}
},
columns: [
{ data: 'users.first_name' },
{ data: 'sites.gisement' }
],
select: true,
buttons: [
{ extend: 'create', editor: usersEditor },
{ extend: 'edit', editor: usersEditor },
{ extend: 'remove', editor: usersEditor }
]
} );
siteTable.on( 'select', function (e) {
usersTable.ajax.reload();
usersEditor
.field( 'users.site' )
.def( siteTable.row( { selected: true } ).data().id );
} );
siteTable.on( 'deselect', function () {
usersTable.ajax.reload();
} );
usersEditor.on( 'submitSuccess', function () {
siteTable.ajax.reload();
} );
siteEditor.on( 'submitSuccess', function () {
usersTable.ajax.reload();
} );
} );```
sites.php file
<?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( 'sites.batiment' ),
Field::inst( 'sites.gisement' )->validator( 'Validate::notEmpty' ),
Field::inst( 'sites.o_habilitation' )
->options( Options::inst()
->table( 'o_habilitation' )
->value( 'id' )
->label( 'libelle_court_hab' )
),
Field::inst( 'o_habilitation.libelle_court_hab' ),
Field::inst( 'sites.description' ),
)
->leftJoin( 'o_habilitation', 'o_habilitation.id', '=', 'sites.o_habilitation' )
->join(
Mjoin::inst( 'users' )
->link( 'sites.id', 'users.site' )
->fields(
Field::inst( 'id' )
)
)
->process( $_POST )
->json();
users.php file
<?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.site' )
->options( 'sites', 'id', 'gisement' )
->validator( 'Validate::dbValues' ),
Field::inst( 'sites.gisement' )
)
->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
->where( 'site', $_POST['site'] )
->process($_POST)
->json();
}
Thank you very much for your answer 
Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
This discussion has been closed.
Answers
Sorry but your code is mostly illegible therefore I cannot respond to your question. Use markdown please.
It has been change
Has someone a idea to put a select field from another datatable (in the example ; o_habilitation) into parent table ?
"In Editor instances that use a join, all fields must have the database table set explicitly."
The first field instance is wrong. Editor cannot know which table you are referring to because you didn't specify it. So is it either "sites.id" or "o_habilitation.id"? Veuillez le préciser. Je vous remercie.
The options instance looks fine by the way: There it is clear that you are only referring to "o_habilitation".
I've solved this problem by precising "sites.id" as you suggested.
However, for a reason thatI can't explaine, I've lost the link between the datatables "sites" and "users".
You can see on the picture below that Json datas is empty. Do you have an idea why ?
Thanks !
That would happen if the data is being filtered out at the server-side for some reason. Could you add
->debug(true)just before the->process(...)call please? Then copy / paste the JSON return from the server into a new message here, and also show your latest code?Thanks,
Allan
here is my file sites.php, it's the only one which has been changed
Json file :
{"data":[]}
Thanks for help.
New Code
Editor::inst( $db, 'IM_ItemVendor' )
->fields(
Field::inst( 'IM_ItemVendor.VendorNo' )->set( false ),
Field::inst( 'IM_ItemVendor.VendorNo' ),
Field::inst( 'ItemCode' ),
Field::inst( 'LastReceiptDate' )
->validator( Validate::dateFormat( 'D, j M y' ) )
->getFormatter( Format::dateSqlToFormat( 'D, j M y' ) )
->setFormatter( Format::dateFormatToSql( 'D, j M y' ) ),
Field::inst( 'LastLeadTime' ),
Field::inst( 'LastReceiptPurchaseOrderNo' )
)
leftJoin( 'AP_Vendor', 'AP_Vendor.VendorNo', '=', 'IM_ItemVendor.VendorNo' )
Error
DataTables warning: table id=IM_ItemVendor - Ajax error. For more information about this error, please see http://datatables.net/tn/7
I Need
SELECT [ItemCode]
FROM [MAS_001].[dbo].[IM_ItemVendor]
left join AP_Vendor ON AP_Vendor.VendorNo=[IM_ItemVendor].VendorNo
What does the server send back? The instructions in that tech note show how you find that information.
Thanks,
Allan