Cannot Edit Nested Tables

Cannot Edit Nested Tables

jaredholdenjaredholden 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

  • allanallan Posts: 61,609Questions: 1Answers: 10,088 Site admin

    It show corrected selected service. But when I choose another service and click update, it delete all services of this customer

    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:

    config: {
      select: {
        style: 'multi'
      }
    }
    

    to your datatable field configuration for the services.

    It don't show any address of customers

    You need to add an Options instance to the id_address field, similar to what you have for id_service in the other Mjoin table.

    Regards,
    Allan

  • jaredholdenjaredholden Posts: 6Questions: 1Answers: 0
    edited May 2023

    Hi,

    Thank you for quickly reply.

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

    {
                    label: 'Service',
                    name: 'table_service[].id_service',
                    type: 'datatable',
            editor: siteEditor,
                optionsPair: {
                    value: 'id_service',
                },      
                config: {
                    ajax: '../php/sitesNested.php',
                    buttons: [
                        { extend: 'create', editor: siteEditor },
                        { extend: 'edit',   editor: siteEditor },
                        { extend: 'remove', editor: siteEditor }
                    ],
                    columns: [
                        {
                            title: 'Name',
                            data: 'service_name'
                        }
                    ]
                }
    }
    
    1. The address is not similar services. Because of the address have multi field: address, city, zipcode, state, phone

    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

  • jaredholdenjaredholden Posts: 6Questions: 1Answers: 0

    To fast, It's my json data:

    {
      "data": [
        {
          "DT_RowId": "row_999",
          "table_user_customer": {
            "id_customer": 999,
            "id_user": 1
          },
          "table_user": {
            "username": "admin"
          },
          "table_customer": {
            "fullname": "FULL NAME 11",
            "birthday": "1988-01-11",
            "email": "EMAIL 1",
            "note": "NOTE 11"
          },
          "default_address": {
            "id_address": 1,
            "address": "ADDRESS 1",
            "city": "CIT 1",
            "state": "STATE 1",
            "zipcode": "ZIPCODE 1",
            "phone": "PHONE 1"
          },
          "table_service": [
            {
              "id_service": 1,
              "service_name": "Home & Kitchen"
            },
            {
              "id_service": 4,
              "service_name": "Health"
            }
          ],
          "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"
            }
          ]
        },
        {
          "DT_RowId": "row_888",
          "table_user_customer": {
            "id_customer": 888,
            "id_user": 1
          },
          "table_user": {
            "username": "admin"
          },
          "table_customer": {
            "fullname": "FULL NAME 2",
            "birthday": "1987-02-14",
            "email": "EMAIl 2",
            "note": "NOTE 2"
          },
          "default_address": {
            "id_address": 2,
            "address": "ADDRESS 2",
            "city": "CITY 2",
            "state": "STATE 2",
            "zipcode": "ZIP CODE 2",
            "phone": "PHONE 2"
          },
          "table_service": [
            {
              "id_service": 3,
              "service_name": "Patio"
            }
          ],
          "table_address": [
            {
              "id_address": 2,
              "address": "ADDRESS 2",
              "city": "CITY 2",
              "state": "STATE 2",
              "zipcode": "ZIP CODE 2",
              "phone": "PHONE 2"
            }
          ]
        }
      ],
      "options": {
        "table_user_customer.id_user": [
          {
            "label": "admin",
            "value": 1
          }
        ],
        "table_service[].id_service": [
          {
            "label": "Health",
            "value": 4
          },
          {
            "label": "Home & Kitchen",
            "value": 1
          },
          {
            "label": "Patio",
            "value": 3
          },
          {
            "label": "Pet",
            "value": 2
          }
        ]
      },
      "files": [],
    

    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?

  • allanallan Posts: 61,609Questions: 1Answers: 10,088 Site admin

    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_customer table. i.e.:

    Editor::inst( $db, 'table_customer', 'id_customer' )
    

    That way, you get one row in the main table per customer, and you can left join to table_user and Mjoin to table_address to show each of the addresses assigned to a customer (which is whay you are trying to do if I understand correctly).

    Regards,
    Allan

  • jaredholdenjaredholden Posts: 6Questions: 1Answers: 0

    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)

  • jaredholdenjaredholden Posts: 6Questions: 1Answers: 0
    edited May 2023

    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

      "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 problem is how to display them in the Editor, with button Create/Edit/Delete?

    I tried:

                    label: 'Address',
                    name: 'table_address',
                    type: "datatable",              
                    config: {
                    buttons: [
                        { extend: 'create', editor: addressEditor },
                        { extend: 'edit',   editor: addressEditor },
                        { extend: 'remove', editor: addressEditor }
                    ],
                        columns: [
                            {
                                title: 'ID',
                                data: 'table_address.id_address'
                            },
                            {
                                title: 'Address',
                                data: 'table_address.address'
                            }
                        ]
                    }
    

    but it don't work :(

  • allanallan Posts: 61,609Questions: 1Answers: 10,088 Site admin

    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)

    Can you not use table_user as the main table in that case? Then link from there to the customer table.

    Allan

  • jaredholdenjaredholden Posts: 6Questions: 1Answers: 0

    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?

  • allanallan Posts: 61,609Questions: 1Answers: 10,088 Site admin

    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 initEdit event 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

Sign In or Register to comment.