Cannot Edit Nested Tables
Cannot Edit Nested Tables
jaredholden
Posts: 6Questions: 1Answers: 0
I have some tables:
`table_customer` (
`id_customer` int(11) NOT NULL,
`fullname` varchar(255) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`note` text NOT NULL
)
`table_address` (
`id_address` int(11) NOT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`zipcode` varchar(12) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL
)
`table_customer_address` (
`id_customer_address` int(11) NOT NULL,
`id_customer` int(11) NOT NULL,
`id_address` int(11) NOT NULL,
`is_default_address` tinyint(1) UNSIGNED DEFAULT NULL,
`is_current_address` tinyint(1) UNSIGNED DEFAULT NULL
)
`table_service` (
`id_service` tinyint(3) UNSIGNED NOT NULL,
`service_name` varchar(255) NOT NULL
)
`table_customer_service` (
`id_customer_service` int(11) NOT NULL,
`id_customer` int(11) NOT NULL,
`id_service` int(11) NOT NULL
)
`table_user` (
`id_user` smallint(6) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`usergroup` tinyint(4) NOT NULL
)
`table_user_customer` (
`id_user_customer` int(11) NOT NULL,
`id_user` int(11) NOT NULL,
`id_customer` int(11) DEFAULT NULL,
`date_add` datetime DEFAULT NULL
)
1 User can manage multi Customers
1 Customer have multi Address
1 Customer have multi Service
Now, I wanna show all information of the customers (default address on homepage and all addresses, service in Editor) have a Manager. I have no problem with Select. I show fine as I want (as the picture)
But in the editor (eg: Edit Customer 1), it don't work:
- It show corrected selected service. But when I choose another service and click update, it delete all services of this customer
- It don't show any address of customers
-
It is my server scrip:
$editor = Editor::inst( $db, 'table_user_customer', 'id_user_customer' )
->fields(
Field::inst( 'table_user_customer.id_customer' )->set(false),
Field::inst( 'table_user_customer.id_user' )
->options( Options::inst()
->table( 'table_user' )
->value( 'id_user' )
->label( 'username' )
),
Field::inst( 'table_user.username' ),
Field::inst( 'table_customer.fullname' ),
Field::inst( 'table_customer.birthday' ),
Field::inst( 'table_customer.email' ),
Field::inst( 'default_address.id_address' ),
Field::inst( 'default_address.address' ),
Field::inst( 'default_address.city' ),
Field::inst( 'default_address.state' ),
Field::inst( 'default_address.zipcode' ),
Field::inst( 'default_address.phone' ),
Field::inst( 'table_customer.note' ),
)
->leftJoin( 'table_user', 'table_user.id_user', '=', 'table_user_customer.id_user' )
->leftJoin( 'table_customer', 'table_customer.id_customer', '=', 'table_user_customer.id_customer' )
->leftJoin(
'table_customer_address',
'table_customer_address.id_customer = table_customer.id_customer AND table_customer_address.is_default_address = 1'
)
->leftJoin(
'table_address as default_address',
'default_address.id_address = table_customer_address.id_address'
)
->join(
Mjoin::inst( 'table_service' )
->link( 'table_user_customer.id_customer', 'table_customer_service.id_customer' )
->link( 'table_service.id_service', 'table_customer_service.id_service' )
->fields(
Field::inst( 'id_service' )
->validator( Validate::required() )
->options( Options::inst()
->table( 'table_service' )
->value( 'id_service' )
->label( 'service_name' )
),
Field::inst( 'service_name' )
)
)
->join(
Mjoin::inst( 'table_address' )
->link( 'table_user_customer.id_customer', 'table_customer_address.id_customer' )
->link( 'table_address.id_address', 'table_customer_address.id_address' )
->fields(
Field::inst( 'id_address' ),
Field::inst( 'address' ),
Field::inst( 'city' ),
Field::inst( 'state' ),
Field::inst( 'zipcode' ),
Field::inst( 'phone' ),
)
)
->debug(true)
->process( $_POST )
->json();
And my js script is:
<script>
var editor; // use a global for the submit and return data rendering in the table_customers
$(document).ready(function() {
var addressEditor = new $.fn.dataTable.Editor( {
ajax: '../faq/controllers/fetch-history.php',
fields: [
{
"label": "Address 1",
"name": "table_address.address"
},
{
"label": "City",
"name": "table_address.city"
},
{
"label": "State",
"name": "table_address.state"
},
{
"label": "Zipcode",
"name": "table_address.zipcode"
},
{
"label": "Phone",
"name": "table_address.phone"
},
]
} );
var serviceEditor = new $.fn.dataTable.Editor( {
ajax: '../faq/controllers/fetch-history.php',
fields: [
{
"label": "Service Name",
"name": "table_service[].service_name"
}
]
} );
editor = new $.fn.dataTable.Editor( {
ajax: "../faq/controllers/fetch-history.php",
table: "#table_customer",
fields: [
{
"name": "table_user_customer.id_customer",
type: "hidden"
},
{
"name": "table_service.id_service",
type: "hidden"
},
{
"label": "Full Name",
"name": "table_customer.fullname"
},
{
"label": "DOB",
"name": "table_customer.birthday"
},
{
"label": "Note",
"name": "table_customer.note"
},
{
"label": "User",
"name": "table_user_customer.id_user",
type: "select"
},
{
label: 'Service',
name: 'table_service[].id_service',
type: 'datatable',
multiple: true
},
{
label: 'Addresses',
name: 'table_address[].id_address',
type: 'datatable',
config: {
buttons: [
{ extend: 'create', editor: addressEditor },
{ extend: 'edit', editor: addressEditor },
{ extend: 'remove', editor: addressEditor }
],
}
}
],
bootstrap: {
floatingLabels: true
}
} );
/*
// Activate an inline edit on click of a table cell
$('#table_customer').on( 'click', 'tbody td:not(:first-child)', function (e) {
editor.inline( this );
} );
*/
$('#table_customer').DataTable( {
columnDefs: [{
"defaultContent": "-",
"targets": "_all"
}],
//serverSide: true,
processing: true,
//lengthChange: false,
dom: "Bfrtip",
ajax: {
url: "../faq/controllers/fetch-history.php",
type: 'POST'
},
order: [[ 1, 'asc' ]],
columns: [
{
data: null,
defaultContent: '',
className: 'select-checkbox',
orderable: false
},
{ data: "table_user_customer.id_customer" },
{ data: "table_customer.fullname" },
{ data: "table_customer.birthday" },
{ data: "table_customer.email" },
{ data: "default_address.address"},
{ data: "default_address.city" },
{ data: "default_address.state" },
{ data: "default_address.zipcode" },
{ data: "default_address.phone" },
{ data: "table_customer.note" },
{ data: "table_service", render: "[, ].service_name" },
{ data: "table_address", render: "[, ].id_address" },
{ data: "table_user.username", editField: "table_user_customer.id_user" },
],
select: {
style: 'os',
selector: 'td:first-child'
},
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
],
} );
} );
</script>
I wanna it work as the picture.
Please help me
Answers
My guess is that when you click "Health", then it will deselect the other two currently selected services. Is that correct? If so, that is because Select is operating in its default "OS" style selection mode. To change it, so it is in multi-row selection mode add:
to your
datatablefield configuration for the services.You need to add an
Optionsinstance to theid_addressfield, similar to what you have forid_servicein the other Mjoin table.Regards,
Allan
Hi,
Thank you for quickly reply.
It's not my problem. Because of I set multiple: true!
{
label: 'Service',
name: 'table_service[].id_service',
type: 'datatable',
multiple: true
}
After debug, I see the problem is the editor update wrong customer id in the table table_customer_service!
I don't know why but I fixed it by change
$editor = Editor::inst( $db, 'table_user_customer', 'id_user_customer' )to
$editor = Editor::inst( $db, 'table_user_customer', 'id_customer' )
Can you explain for me why? Because of the column id_user_customer is PRIMARY KEY, not id_customer
Now, I can update the list of service. how can I add action button for Service (New/Edit/Delete).
I tried the example https://editor.datatables.net/examples/datatables/nested.html but in the example, the user.site in main query. But my table_service in mjoin query so that it don't work. My code is
I wanna show it like that (I use normal HTML to create the demo)
(with button New/Edit/Delete)
Can you please help me write the code with Datatables Editor? I'm ready to buy the license if it work as I want
It is link download my code + database https://1drv.ms/u/s!At0eJtwWr2SMakP-iglMU55T850?e=7DucmY Please check it to easy help me solve the issue
To fast, It's my json data:
Now, In the Editor, I wanna show all the addresses of a customer like that
And have option Create/Edit/Delete Buttion. And I wanna add a ajax form to assign exist address (from table table_addresss) to this customer
Can I do it with Datatable Editor?
Oh - I hadn't realised that you'd split a "customer" over multiple tables, and were using a 1:1 relationship between them, rather than just having one larger table. I do see that now.
I think you actually need your main table to be the
table_customertable. i.e.:That way, you get one row in the main table per customer, and you can left join to
table_userand Mjoin totable_addressto show each of the addresses assigned to a customer (which is whay you are trying to do if I understand correctly).Regards,
Allan
Hi,
The problem is Datatables don't support INNER JOIN while my table_customer have big data (about 1 million records). If table_customer is main table, it will overload and I only wanna get the customer managed by a manager (table_user)
I sent my code & my database in above reply and also in the email too. Can you please help me show each of the addresses assigned to a customer (and have button Create/Edit/Delete)
And my problem isn't cannot get each of the addresses assigned to a customers. Please see my picture and my json response. I got them success
My problem is how to display them in the Editor, with button Create/Edit/Delete?
I tried:
but it don't work
Can you not use
table_useras the main table in that case? Then link from there to the customer table.Allan
What is happen if I use table_user as the main table? I think the output still same
"table_address": [
{
"id_address": 1,
"address": "ADDRESS 1",
"city": "CIT 1",
"state": "STATE 1",
"zipcode": "ZIPCODE 1",
"phone": "PHONE 1"
},
{
"id_address": 3,
"address": "ADDRESS 3",
"city": "CITY 3",
"state": "STATE 3",
"zipcode": "ZIPCODE 3",
"phone": "PHONE 3"
}
My question is how to display it in the Editor?
Right - I think I'm starting to get what you are aiming for now. Apologies for not understanding before.
If we take this Editor example, which shows nested editing: that is not what you want is it? It shows a list of the options and the user just picks from one.
You want something like this example where the options in the child table are dependent on the row that is selected for editing.
If you look at that example's code (shown below the table on that page), you will see a
initEditevent handler that will make a call to the server to get the list of options to show in the nested table.There is no option at this time for having the list of options be dynamic best of the row's data without making that extra call (i.e. it can't be populated by the data from the initial Ajax request that DataTables makes). If I've now understood correctly, this is the key point, since it doesn't work quite the way you are looking to do it. It does it a different way with the Ajax call to get the nested data.
Does that help a bit now?
Allan