Nested Editing Example

rf1234

https://editor.datatables.net/examples/datatables/nested.html

I guess the server script shown is this: '../php/joinNested.php'

I would also like to see this script: '../php/sitesNested.php'

Where can I see it?

kthorngren

I believe these files are in the Editor download. Look here:

Editor-PHP-x > Controllers

Kevin

rf1234

Thanks Kevin. I have just realized that I am using the "datatable" field type quite differently than suggested in the docs. In the example you are working on a child table "users" and assign a "site" from the parent table which is saved as a foreign key in the child table ("users.site").
That is opposite of what I am doing:
I have a parent table "sub" and I want to define cashflows for "sub" which are saved in a child table "sub_exec_cashflow". I got it working for "edit" when I can pass the parent id to the child table. I wonder whether I could get it working for "create" as well. Question is: How can I implicitly pass the required id to the child table on "create" of the parent table? (actually it's two fields "ctr_id" and "is_sub_provider")

Here is my Editor code:

var subExecCashflowEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubExecCashflow',
        data: function ( d ) {
            d.ctr_id = parentId;
            d.is_sub_provider = parentIsSubProvider;
        }
    },
    fields: [
        {
            label: lang === 'de' ? 'Frist Mittelabruf:' : 'Deadline Funds Call:',
            name: "sub_exec_cashflow.cashflow_due_date",
            attr: {
                class: dateMask,
                placeholder: "optional"
            },
            type: "datetime",
            format: 'L',
            opts: {
                showWeekNumber: true,
                momentLocale: momentLocale
            }
        }, {
            label: lang === 'de' ? 'Abrufbetrag:' : 'Call Amount:',
            name:  "sub_exec_cashflow.cashflow_amount",
            attr: {
                class: amountMask,
                placeholder: "optional"
            }
        }
    ]
});

var subTablesEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubReg'
    },
    table: "#tblSubReg",
    i18n: { edit: { title: lang === 'de' ? 'Basistermine bearbeiten' : 'Edit Basis Due Dates' } },
    formOptions: {
        main: {
            focus: 1
        }
    },
    fields: [
         {
            label: lang === 'de' ? 'Maßnahme:' : 'Measure:',
            name:  "sub.sub_name",
            type:  "readonly"
        }, {
            label: lang === 'de' ? 'Mittelabrufe:' : 'Calls for Funds:',
            name: 'sub_exec_cashflow[].id',
            type: 'datatable',
            editor: subExecCashflowEditor,
            config: {
                searching: false,
                fixedHeader: false,
                paging: false,
                scrollY: "150px",
                scrollCollapse: true,
                stateSave: false,
                ajax: {
                    url: 'actions.php?action=tblSubExecCashflow',
                    type: 'POST',
                    data: function ( d ) {
                        d.ctr_id = parentId;
                        d.is_sub_provider = parentIsSubProvider;
                    }
                },
                language: languageEditorDts,
                buttons: [
                    { extend: 'create', editor: subExecCashflowEditor },
                    { extend: 'edit',   editor: subExecCashflowEditor },
                    { extend: 'remove', editor: subExecCashflowEditor }
                ],
                order: [[0, 'asc']],
                columns: [
                    {   title: lang === 'de' ? 'Frist Mittelabruf' : 'Deadline Funds Call',
                        data: 'sub_exec_cashflow.cashflow_due_date'         },
                    {   title: lang === 'de' ? 'Abrufbetrag' : 'Call Amount',
                        data: 'sub_exec_cashflow.cashflow_amount'           }
                ]
            }
        }, {
            type: "hidden",
            name: "ctr.soft_deleted"
        }, {
            type: "hidden",
            name: "ctr.id"
        }, {
            type:  "hidden",
            name:  "ctr.serial"
        }, {
            type:  "hidden",
            name:  "sub.is_sub_provider"
        }
    ]
});

allan

Hi Roland,

In the example you are working on a child table "users" and assign a "site" from the parent table which is saved as a foreign key in the child table ("users.site").

Interestingly, I use the terminology that "users" is the parent table and "sites" is the child table, since the user's table is the one that is being displayed as the main table and the sites table as the child.

As a result, I might be misunderstanding the question, apologies if so, but, my form of the terminology:

Question is: How can I implicitly pass the required id to the parent table on "create" of the child table? (actually it's two fields "ctr_id" and "is_sub_provider")

With a single value, that is what the datatable field type is doing is it not? You select a value from the list (including any newly created ones) and that is what gets submitted to the host (what I call the parent).

Let's take this example.

If I click the New button, and then in the nested table select New again, I can create a new row, which has an id / value. That becomes the value of the field for the parent / host table.

Perhaps the issue is that you are looking for two values (perhaps a compound key?). The datatable field type does not support having two values for different fields - it is a single field input only.

Allan

rf1234

Hi Allan, thanks!

Let me clarify. For me "parent - child" is derived from Entity-Relationship-Modeling exclusively. In that regard "users" is the child because it holds the id from "sites" as foreign key. Also: One site can have many users, but one user can only have one site. That is traditional "parent - child". Just like in the 1960's when IBM published its hierarchical database IMS DB. This database could only do 1:N and not N:M like modern relational databases that use a link table for that. (Reading the database you had commands like "get next within parent" etc. if I recall it correctly. Was a little bit better than indexed files but not much.)

What I want to do is the following (in the words of your example):
Creating a new "site" I want to create new "users" working on that site in one go! What does that mean? When inserting the child table rows I need to know the parent site_id (which you call "users_site" I guess). Right now I don't have it and I have no way to pass it unless I created the site before hand.

This would need to be done by Editor:
- Editor would need to insert the parent table row first ("sites"),
- get the "lastInsertId" and
- use that "lastInsertId" as the foreign key to insert the n child rows (new users).

What would I need to "tell" Editor to get this done?
- names of the source and target variables (source: new sited.id, target: new users.sites_id (= foreign key))
- Those names should be passed in an array: hence you have more than just one foreign key field if you will. (that would also cover my use case).

It would be awesome if this could be achieved!

rf1234

Forgot to mention: I am already doing all of this! But with a trick. The trick is I split the creation process of a new "site" into several steps. After the first step the "site" already exists in the database - even though it is still incomplete.

This trick allows me to use field type "datatable" in one of the following steps: I can pass the "lastInserId" from "sites" when inserting the new "users" working on the "site". You can see that in my code above and in this picture. "d.ctr_id" is nothing but the "lastInsertId".

For the sake of completeness this is the code of the Editor for field type "datatable":

if ( ! isset($_POST['ctr_id']) || ! is_numeric($_POST['ctr_id']) ) {
    echo json_encode( [ "data" => [] ] );
} else {
    Editor::inst( $db, 'sub_exec_cashflow' )
    ->field(
        Field::inst( 'sub_exec_cashflow.ctr_id' )->set(Field::SET_CREATE)
                                                 ->setValue( filter_var($_POST['ctr_id']) ),
        Field::inst( 'sub_exec_cashflow.cashflow_due_date' )
            ->getFormatter( function ( $val, $data, $opts ) {
                return getFormatterDate($val);                   
            } )
            ->setFormatter( function ( $val, $data, $opts ) {
                return setFormatterDate($val);
            } ),
        Field::inst( 'sub_exec_cashflow.cashflow_amount' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                return setFormatterSubAmount($val, filter_var($_POST['is_sub_provider'])); //are we providing or receiving the subsidy?
            }),
        Field::inst( 'sub_exec_cashflow.cashflow_amount as unformattedAmount' )->set( false )
    )
    ->leftJoin( 'ctr', 'sub_exec_cashflow.ctr_id', '=', 'ctr.id')
    ->where( function ( $q ) {        
        $q  ->where( 'sub_exec_cashflow.ctr_id', $_POST['ctr_id'] );
    } )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
        logChange( $editor->db(), 'create', $id, $row, 'sub_exec_cashflow' );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $row, 'sub_exec_cashflow' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'sub_exec_cashflow' );
    } )
    ->on( 'postGet', function ( $e, &$data, $id ) use ( $lang ) { 
        if ( count($data) <= 0 ) {
            return;
        }
        $sum = array_sum(array_column($data, "unformattedAmount"));
        $keys = array_keys($data);
        foreach ( $keys as $key ) {
            unset($data[$key]["unformattedAmount"]);
        }      
        if ( $sum != 0 ) {
            $data[] = [ "DT_RowId" => "row_0", //in reality there is no row 0 because it is derived from the SQL id
                        "sub_exec_cashflow" => 
                            [ "ctr_id"            => $_POST['ctr_id'], 
                              "cashflow_due_date" => $lang === "de" ? "Summe" : "Total",
                              "cashflow_amount"   => getFormatterAmount($sum) ] ];
        }
    })
    ->process($_POST)    
    ->json();
}

rf1234

And another one

To make my suggestion work when creating the parent table entry you would need a joint submission of the new parent table record and all of the child records as defined in field type "datatable".

It seems to me that this joint submission simply has not been part of the design: Whenever you create or edit a child record with field type "datatable" it is immediately submitted to the server and hence this cannot work before the parent table entry exists! Hmmmm ...

So what is really needed is a new field type "datatableChildren" that buffers all of the child rows on the client side and submits them jointly with the parent table record! Then the parent record is inserted first and the child rows are inserted using "lastInsertId" as foreign key.

For editing it could be left as is because in that case the foreign key is already known.

So that would be my requirement

allan

Whenever you create or edit a child record with field type "datatable" it is immediately submitted to the server and hence this cannot work before the parent table entry exists! Hmmmm ...

If the join reference is in the nested table, then yes, that is absolutely correct.

Taking our example when you create a new site, you don't need to know the user id (the user table links to the site).

Where that doesn't work is if you list the sites with a one-to-many join to the user's table. Correct me if I'm wrong please, but that is what you are looking for here isn't it?

Clever workaround to address this! I didn't have any plans to implement something for this in Editor, but I absolutely can see how it would be useful. I'll consider how we can add something like this in future. Many thanks for the suggestion!

Allan

rf1234

Thanks a lot Allan! You are doing a great job! The richness of your software can be overwhelming sometimes!

If the join reference is in the nested table, then yes, that is absolutely correct.

Yes, and that is my standard use case. You have a parent and the nested table entries contain the join reference (i.e. the foreign key from the parent table). I know that your examples don't have my use case. No worries - we'll get that resolved.

I think I already have a solution for my parent - child editing using field type "datatable". I only need this solution when I want to "create" the parent table record and the N child records (field type "datatable", nested editing) in one go.

This is how it could work:
- fill out the fields for the parent record in Editor
- do local table editing of the child tables (when editing field type "datatable", nested editing)
- "on SubmitSuccess" of the parent record: Submit the buffered child table records to the server either using Editor or by simply inserting them "manually" myself with a proprietary ajax call.

In case the local child tables get destroyed on "close" of the main editor form I would need to capture them at that time. Do they get destroyed at that time?

There is only one issue: How can I turn local table Editing on and off dynamically?

I tried the code below, but it didn't work. Yes, no server update was made, but the record didn't show up in the table either. I needed to completely remove the "ajax" option to make it work, but that can't be done dynamically, can it? Is there a trick or work around?

var subExecCashflowEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubExecCashflow',
        data: function ( d ) {
            if (d.action === 'create') {
                delete d;
            } else {
                d.ctr_id = parentId;
                d.is_sub_provider = parentIsSubProvider;
            }            
        }
    },
    fields: [
        {
            label: lang === 'de' ? 'Frist Mittelabruf:' : 'Deadline Funds Call:',
            name: "sub_exec_cashflow.cashflow_due_date",
            attr: {
                class: dateMask,
                placeholder: "optional"
            },
            type: "datetime",
            format: 'L',
            opts: {
                showWeekNumber: true,
                momentLocale: momentLocale
            }
        }, {
            label: lang === 'de' ? 'Abrufbetrag:' : 'Call Amount:',
            name:  "sub_exec_cashflow.cashflow_amount",
            attr: {
                class: amountMask,
                placeholder: "optional"
            }
        }
    ]
});

rf1234

In case the local child tables get destroyed on "close" of the main editor form I would need to capture them at that time. Do they get destroyed at that time?

How can I turn local table Editing on and off dynamically?

@allan @colin
Can you answer those questions from my post above, please?

allan

Oops - sorry - I missed those!

In case the local child tables get destroyed on "close" of the main editor form I would need to capture them at that time. Do they get destroyed at that time?

No - the child DataTable is initialised once and gets reused, potentially getting its options changes between uses. It is very similar to the select field type in this regard. So if you need the DataTable on close, you can use editor.field('myDT').dt() in the close event handler or at any other point to get an API instance for it.

How can I turn local table Editing on and off dynamically?

There isn't an API to do that at the moment I'm afraid. If you fancy a little hack to make it would you could do editor.s.ajax = null to turn it off, and editor.ajax({ ... }) to set an Ajax configuration. I could look at making null allowable in ajax() to disable Ajax - I'm a little concerned about the async actions and doing that just too early or too late though.

Perhaps try the hack and see how that goes for you and I could put that in if it works - I think you'd be the only one using that specific feature for the time being

Allan

rf1234

Thanks a lot, Allan!

I think you'd be the only one using that specific feature for the time being

That is really surprising. For me this is THE standard use case in any business I can think of: A project (parent) with many activities (children).

During the 20+ years that I worked as a business executive my focus was on getting things (= projects) done (= many activities)!