Editor 2 nested editing

mguinness

I'm hoping that the new datatable field type will allow for the following scenario, but if not it would be a nice feature to have.

I have a database table which contains a JSON field. Based on what I have seen, nested editing only seems to be for lookups where you can select either a single or multiple rows from a predefined list using the options attribute.

For example, a sample record below:

Id	Company	Offices
1	Acme Inc.	[{"name":"New York","employees":12}, {"name":"Chicago","employees":5}, {"name":"Los Angeles","employees":3}]

I would like the main editor to be for company (with id as key), then a nested datatable for name and employees (from offices JSON). I don't think I can configure this out of the box, but would it be possible to achieve via custom code?

The data it would receive from the server would be the following:

[{"id":1,"company":"Acme Inc.","offices":[{"name":"New York","employees":12}, {"name":"Chicago","employees":6}, {"name":"Los Angeles","employees":3}]}]

And based on Client-to-server in documentation it would submit:

{"data":{"1":{"company":"Acme Inc.","offices":[{"name":"New York","employees":12}, {"name":"Chicago","employees":6}, {"name":"Los Angeles","employees":4}] }},"action":"edit"}

Is this something that is possible and if so, do you have an example? If not, is this something that can be done with Javascript using events? I guess this isn't the demonstrated use case, but it would be very useful to be able to do.

allan

Do you mean something like this? It is basically a fully editable nested table and can be configured as such.

Allan

mguinness

Thanks for your reply Allan. I looked at both Nested editing and Multiple selection examples in Embedded DataTable section.

I'm probably not being clear at what my issue is. I have no problem configuring the nested table, it's that I want to control loading and saving of data not via ajax but the JSON field. In the example you provided the POST is as follows after an edit.

As you can see data[row_32][users][site] is only showing the selected key value. I would need that to be all fields encoded as JSON. Conversely on record edit I would need the JSON value to populate the embedded DataTable on load.

The only way I think I could do this is to use a hidden field for the JSON value and use that in preOpen to populate the table and then in preClose to save data() back into the hidden field.

Maybe I'm missing something obvious, but hopefully you can point me in the right direction.

colin

I see you're using Editor in your question, but our accounts aren't showing that you have a license. Is the license registered to another email address? Please can let us know so we can update our records and provide support.

Thanks,

Colin

mguinness

@colin I sent you a DM with the details. Back to the issue at hand, is what I want to do possible? The existing examples all appear to be option pickers. I think this is a valid use case.

allan

Ah - I think I see. You are looking for something like this, but just nested? That should work out of the box, as long as the nested Editor is able to get the options it needs (typically from its own Ajax request).

If that isn't working, could you give me a link to your page showing the issue please?

Allan

mguinness

Thanks, I will give it a try since you think it's feasible. I assume that neither options and optionsPair will be necessary in this situation.

In an similar vein, is it possible to do parent/child editing with the new datatable field type similar to Parent / child editing in child rows?

colin

I think this example is demonstrating your second paragraph question,

Colin

mguinness

Thanks Colin, but that example shows editing a common lookup table not associated records with a foreign key to the current record.

mguinness

Below is some sample code to allow editing of a JSON field. Since the data in the JSON array has no key it was necessary to append a dummy key as DT_RowId (to permit editing) before loading it into the datatable field options.

        var officeEditor = new $.fn.dataTable.Editor({
            fields: [{
                label: "Name:",
                name: "name",
            }, {
                label: "Employees:",
                name: "employees",
                attr: {
                    type: "number"
                }
            }]
        });

        var editor = new $.fn.dataTable.Editor({
            ajax: {
                url: "/api/Save",
                type: "POST",
                contentType: "application/json",
                data: function (d) {
                    var data = editor.field('offices').dt().data();
                    data.map(function (ele) {
                        return ele.DT_RowId = undefined;
                    });
                    d.data[Object.keys(d.data)[0]].offices = data.toArray();
                    return JSON.stringify(d);
                }
            },
            table: "#example",
            idSrc: "id",
            fields: [
                {
                    label: "Company:",
                    name: "company"
                }, {
                    label: "Offices:",
                    name: "offices",
                    type: "datatable",
                    editor: officeEditor,
                    config: {
                        searching: false,
                        buttons: [
                            { extend: 'create', editor: officeEditor },
                            { extend: 'edit', editor: officeEditor },
                            { extend: 'remove', editor: officeEditor }
                        ],
                        columns: [
                            { title: 'Name', data: 'name' },
                            { title: 'Employees', data: 'employees' }
                        ]
                    }
                }
            ]
        });

        editor
            .on('open', function (e, mode, action) {
                if (action === 'create')
                    editor.field('offices').update([]);
                else {
                    var data = table.row(editor.modifier()).data();
                    if (data.offices) {
                        data.offices.map(function (ele) {
                            return ele.DT_RowId = crypto.randomUUID();
                        });
                        editor.field('offices').update(data.offices || []);
                    }
                    else
                        editor.field('offices').update([]);
                }
            });

        var table = $('#example').DataTable({
            "searching": false,
            "select": {
                style: 'single',
                info: false 
            },
            "ajax": {
                "url": '/api/GetData',
                "dataSrc": ''
            },
            "columns": [
                { "data": "id", "title": "Id" },
                { "data": "company", "title": "Company" }
            ],
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor }
            ]
        });

Then once the record is being saved that dummy key is removed before posting data to the server. It's not particularly elegant but it works. Any suggestions to improve or streamline the code would be welcome.

allan

Nice one - thanks for sharing this with us! I think this is probably as good as it gets at the moment for something like this. We'll take it onboard and look at options for how we can integrate that more in Editor.

If anyone else reads this and is interested in direct JSON editing of the value, please let us know!

Regards,
Allan

mguinness

Thanks for reviewing and letting me know that I'm on the right track. I noticed that there is internal code to autogenerate a value for DT_RowId when a new record is created, is that something that is exposed via the library or can you share a code snipped on how that is generated?

mguinness

I was able to find the code to autogenerate a key value, it basically uses the Date() constructor returning a milliseconds value. I've also moved code from the ajax function to the preSubmit event instead, as it seems a cleaner approach.

editor
    .on('open', function (e, mode, action) {
        if (action === 'create')
            editor.field('offices').update([]);
        else {
            var data = table.row(editor.modifier()).data();
            if (data.offices) {
                data.offices.map(function (ele, idx) {
                    return ele.DT_RowId = +new Date() + idx.toString();
                });
                editor.field('offices').update(data.offices || []);
            }
            else
                editor.field('offices').update([]);
        }
    })
    .on('preSubmit', function (e, data, action) {
        var items = editor.field('offices').dt().data();
        items.map(function (ele) {
            return ele.DT_RowId = undefined;
        });
        data.data[Object.keys(data.data)[0]].offices = items.toArray();
    });

colin

Nice, thanks for posting back,

Colin

Loren Maxwell

@allan -- "If anyone else reads this and is interested in direct JSON editing of the value, please let us know!"

I second the request for an out-of-the-box Datatable control option to load/save JSON data!

Another example might be a running series of notes pertaining to a specific record.

Would be nice to be able to make a new entry with the date, the note, the id of the person making the note, etc., and store them all in a JSON field without a separate "Notes" table in the database.

A Datatable control with that option seems to be the best solution.

rf1234

@allan - I see that other people also want to use field type "datatable" for parent child editing. Not just me. I have a work around that requires deleting the "datatable" field of the parent table, but apart from that my solution works as it should. Maybe you could add parent - child editing to the Editor "standard" if you know what I mean.
https://datatables.net/forums/discussion/comment/210205#Comment_210205

As @mguinness noted you are not covering real parent - chidl editing right now:

Thanks Colin, but that example shows editing a common lookup table not associated records with a foreign key to the current record.

There has been so much confusion about parent - child editing in this forum. @mguinness definition "associated records with a foreign key to the current record" is precisely what I mean.

Implementing parent - child editing with field type "datatable" would be the first prerequisite to get JSON field editing running.

It is pretty simple actually:
- Using Editor at the back end you split the JSON field into individual fields that you return to the server.
- On editing in field type "datatable" you just use one of the PHP events to put the individual fields back into the required JSON structure and update the database
- all of this would be done in the Editor instance of field type "datatable".

rf1234

Here is a post that highlights how to tear the JSON field apart on the server in order to return the individual fields to the client:
https://datatables.net/forums/discussion/comment/168181/

Loren Maxwell

I'm on my way out the door -- otherwise I would try to make a test case. I can try later this evening but I wanted to see if anyone has any quick answers :-)

Anyway, I'm using @mguinness last code snippet above to also store values from a Datatable input in a JSON database field.

Everything works perfectly (i.e., I can add, edit, and delete the entries and they are saved in the JSON field correctly) EXCEPT in the special case of deleting all the entries for the JSON field, leaving an empty array [] as the correct entry into the database. In this case, no update is passed along, leaving the JSON database field untouched.

In other words, I cannot delete all the JSON entries to leave an empty array [].

The presubmit seems to work correctly:

editor
    .on('open', function (e, mode, action) {
        if (action === 'create')
            editor.field('offices').update([]);
        else {
            var data = table.row(editor.modifier()).data();
            if (data.offices) {
                data.offices.map(function (ele, idx) {
                    return ele.DT_RowId = +new Date() + idx.toString();
                });
                editor.field('offices').update(data.offices || []);
            }
            else
                editor.field('offices').update([]);
        }
    })
    .on('preSubmit', function (e, data, action) {
        var items = editor.field('offices').dt().data();
        items.map(function (ele) {
            return ele.DT_RowId = undefined;
        });
        data.data[Object.keys(data.data)[0]].offices = items.toArray();
console.log(data.data[Object.keys(data.data)[0]].offices ) // Correctly shows [] when all entries have been deleted from the Datatable input
    });

But in the payload sent to the server, offices field is missing (again, only in the special case of an empty array [], otherwise it's correct).

My guess is that there is something after the presubmit and before it is sent to the server that is removing the offices field if it contains only an empty array [].

Is presubmit the correct event? Or is there something else I should be doing to update the offices field to show an empty array []?

allan

Yes, preSubmit is the correct place to manipulate the data that is being sent t the server.

The thing with an empty array is that when being sent by http parameters (which Editor does by default) it basically means that there is no parameter to send - which is exactly what you are seeing. e.g. you can't send office: since that would mean that there is an entry! This is the exact reason that Editor sends a special -many parameter for array fields (if you've seen that before).

So ideally, you'd send a parameter with the length of the offices array. Its a pain, but that's a workaround for using http parameter.

The alternative is to use JSON - e.g. data.json = JSON.stringify(data) and then parse that at the server. That has the benefit of preserving typing and array information.

Allan

Loren Maxwell

Thanks, @allen -- I used the JSON.stringify(data) and parse at the server approach.