DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

One-to-many nested editors

One-to-many nested editors

IT CMSIT CMS Posts: 10Questions: 1Answers: 0
in Editor

Hi everyone,

Maybe I missed something, but I can't find a way to do it.

Here are more details on what I'm trying to do:

I have two tables:
- Table "items"
- item_id
- item_name
- ...
- Table "subitems":
- subitem_id
- item_id
- subitem_name
...

I want to be able to perform CRUD operations on my subitems directly from the "items" editor.

My first idea was to use a nested editor like this: https://editor.datatables.net/examples/datatables/nested (or multiple version).
However, that isn't the solution because the nested editor lists all the "subitems", and the user needs to select them.
I only want to be able to CRUD "subitems" linked to the current "item" through the "subitems.item_id".

Is there a way to do that with a nested editor?

If not, how could I do it?

Thanks, Renaud

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 65,755Questions: 1Answers: 10,939 Site admin
    Answer ✓

    Hi Renaud,

    The correct approach will depend upon the exact relationship between the items and subitems table. Can there be multiple subitems entries assigned to a single items entry for example?

    If so, then have a look at this parent child editing example which I think will match what you need.

    Allan

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Hi Allan,

    Thanks for your answer.
    That look very interresting :smile:

    Just one question, there is no "server script" in the example, do you have an exemple of it ?

    Renaud

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Sorry I didn't answer your question, yes, it will be multiple subitems assigned to a single Items.

  • allanallan Posts: 65,755Questions: 1Answers: 10,939 Site admin
    Answer ✓

    The controllers used for that demo (and the other examples) are available in the PHP download package. I've put them in below as well:

    sites.php:

    <?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( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();

    users.php:

    <?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.last_name' ),
            Field::inst( 'users.phone' ),
            Field::inst( 'users.site' )
                ->options( 'sites', 'id', 'name' )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'sites.name' )
        )
        ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
        ->where( 'site', $_POST['site'] )
        ->process($_POST)
        ->json();
}

    As you'll see the only departure from a normal Editor server-side file is the where condition in users.php and the check there to make sure that something was submitted.

    Allan

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Hi Allan,

    Thanks that perfect :smiley:

    Renaud

  • allanallan Posts: 65,755Questions: 1Answers: 10,939 Site admin

    You are most welcome :)

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Arrrgg, I speak too fast.

    Your solution is still perfect :wink:
    I got an error when I click on the child button :

    dataTables.editor.min.js:6 Uncaught TypeError: Cannot read properties of null (reading 'one')
    at X.action (dataTables.editor.min.js:6:84112)
    at s (dataTables.buttons.min.js:4:7052)
    at r (dataTables.buttons.min.js:4:7286)
    at HTMLButtonElement.<anonymous> (dataTables.buttons.min.js:4:7533)
    at HTMLButtonElement.dispatch (jquery-3.7.1.js:5145:27)
    at elemData.handle (jquery-3.7.1.js:4949:28)
    action @ dataTables.editor.min.js:6
    s @ dataTables.buttons.min.js:4
    r @ dataTables.buttons.min.js:4
    (anonymous) @ dataTables.buttons.min.js:4
    dispatch @ jquery-3.7.1.js:5145
    elemData.handle @ jquery-3.7.1.js:4949

    Are you aware of a bug in button ?

    Renaud

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Here the html, javascript and CSS code :

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
    <title>Prompts Admin</title>
    <meta http-equiv='Content-type' content='text/html; charset=utf-8'>
    <link rel="stylesheet" type='text/css' href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" type='text/css' href="https://code.jquery.com/ui/1.14.2/themes/base/jquery-ui.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/2.3.8/css/dataTables.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/3.2.6/css/buttons.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/datetime/1.6.3/css/dataTables.dateTime.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/fixedheader/4.0.6/css/fixedHeader.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/3.1.3/css/select.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css">    
    <link rel="stylesheet" type="text/css" href="/lib_datatables_editor/css/editor.dataTables.min.css">
    <style type="text/css">
        div.DTE_Field {
            padding-top: 10px !important;
            padding-bottom: 10px !important;
        }
        div.DTED_Lightbox_Wrapper {
            width: 1600px;
            margin-left: -800px;
        }
        div.DTE_Body div.DTE_Body_Content div.DTE_Field {
            padding: 5px;
        }
        div.DTE_Body div.DTE_Body_Content div.DTE_Field>label {
            width: 15%;
        }
        div.DTE_Body div.DTE_Body_Content div.DTE_Field>div.DTE_Field_Input {
            width: 85%
        }
        td.editor-edit button,
        td.editor-delete button {
            background: transparent;
            border: none;
            color: inherit;
            cursor: pointer;
            margin-right: 0.5em;
        }
    </style>
    <script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-3.7.1.js"></script>
    <script src="https://cdn.tiny.cloud/1/ujtg872nrndqamd09y0i4j4y77bxsx1xksaikt2bhallps3n/tinymce/8/tinymce.min.js" referrerpolicy="origin" crossorigin="anonymous"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/2.3.8/js/dataTables.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.2.6/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.2.6/js/buttons.colVis.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/datetime/1.6.3/js/dataTables.dateTime.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/fixedheader/4.0.6/js/dataTables.fixedHeader.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/select/3.1.3/js/dataTables.select.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js"></script>
    <script type="text/javascript" language="javascript" src="/lib_datatables_editor/js/dataTables.editor.min.js"></script>
    <script type="text/javascript" language="javascript" src="/lib_datatables_editor/js/editor.select2.js"></script>
    <script type="text/javascript" language="javascript" src="/lib_datatables_editor/js/editor.tinymce.js"></script>
  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0 
    <script type="text/javascript" language="javascript" class="init">
        var editor_main;
        var editor_prompts;
        var table_main;
        var cur_prompt_id;
        $(document).ready(function() {
            var editor_main = new DataTable.Editor( {
                ajax: "./ajax.php",
                table: "#prompts",
                fields: [
                    {
                        label: "Outils :",
                        name: "outils",
                        type: "select2",
                        fieldInfo: "Txt",
                        opts: {
                            multiple: true,
                            allowClear: true
                        },
                        separator: ',',
                        options: [
                            <?php
                                foreach($outils as $k=>$v) {
                                    echo '{ "label": "'.$v.'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },{
                        label: "Catégories :",
                        name: "categories[].id",
                        type: "select2",
                        fieldInfo: "Txt",
                        opts: {
                            multiple: true,
                            allowClear: true
                        },
                        separator: ',',
                        options: [
                            <?php
                                foreach($categories as $k=>$v) {
                                    echo '{ "label": "'.$v["path"].'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },{
                        label: "Statut :",
                        name: "statut",
                        type: "select",
                        fieldInfo: "Txt",
                        options: [
                            <?php
                                foreach($statuts as $k=>$v) {
                                    echo '{ "label": "'.$v.'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },{
                        label: "Niveau :",
                        name: "niveau",
                        type: "select",
                        fieldInfo: "Txt",
                        options: [
                            <?php
                                foreach($niveaux as $k=>$v) {
                                    echo '{ "label": "'.$v.'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },{
                        label: "Owner :",
                        name: "user_owner",
                        type: "select",
                        fieldInfo: "Txt",
                        options: [
                            <?php
                                foreach($users as $k=>$v) {
                                    echo '{ "label": "'.$v["FirstName"].' '.$v["LastName"].'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },{
                        label: "Merci à :",
                        name: "users_merci",
                        type: "select2",
                        fieldInfo: "Txt",
                        opts: {
                            multiple: true,
                            allowClear: true
                        },
                        separator: ',',
                        options: [
                            <?php
                                foreach($users as $k=>$v) {
                                    echo '{ "label": "'.$v["FirstName"].' '.$v["LastName"].'", "value": "'.$k.'" },';
                                }
                            ?>
                        ]
                    },
                    {
                        label: "URL :",
                        name: "prompts.url",
                        fieldInfo: "Txt"
                    },{
                        label: "Liste des prompts :",
                        name: "prompts_prompts.prompt_id",
                        type: "datatable",
                        editor: editor_prompts,
                        submit: false,
                        optionsPair: {
                            value: 'prompts_prompts.id'
                        },
                        config: {
                            ajax: {
                                url: './ajax_prompts.php',
                                type: 'POST',
                                data: function (d) {
                                    d.prompt_id = cur_prompt_id;
                                }
                            },
                            buttons: [
                                { extend: "create", editor: editor_main, text: 'Ajout' },
                                { extend: "edit",   editor: editor_main, text: 'Modification' },
                                { extend: "remove", editor: editor_main, text: 'Suppression' },
                            ],
                            columns: [
                                {
                                    title: 'Prompt ID :',
                                    data: 'prompts_prompts.prompt_id'
                                },
                                {
                                    title: 'Titre :',
                                    data: 'prompts_prompts.titre'
                                },
                                {
                                    title: 'Prompt :',
                                    data: 'prompts_prompts.prompt'
                                }
                            ]
                        }
                    },{
                        label: "Objectif :",
                        name: "objectif",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    },{
                        label: "Contexte :",
                        name: "contexte",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    },{
                        label: "Attentes :",
                        name: "attentes",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    },{
                        label: "Configuration :",
                        name: "configuration",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    },{
                        label: "Attention :",
                        name: "attention",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    }
                ]
            } );

            var editor_prompts = new DataTable.Editor( {
                ajax: {
                    url: './ajax_prompts.php',
                    type: 'POST',
                    data: function (d) {
                        d.prompt_id = cur_prompt_id;
                    }
                },
                fields: [
                    {
                        label: "Prompt ID:",
                        name: "prompt_id",
                        type: "hidden",
                    },{
                        label: "Titre :",
                        name: "titre",
                        fieldInfo: "Txt"
                    },{
                        label: "Prompt :",
                        name: "prompt",
                        type: "tinymce",
                        fieldInfo: "Txt",
                        "opts": {
                            promotion: false,
                            onboarding: false,
                            branding: false,
                            plugins: [
                                'advlist', 'anchor', 'autolink', 'charmap', 'code', 'fullscreen',
                                'help', 'image', 'insertdatetime', 'link', 'lists', 'media',
                                'preview', 'searchreplace', 'table', 'visualblocks', 'wordcount'
                            ],
                            toolbar: 'undo redo | styles | bold italic underline strikethrough | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image | code | fullscreen',
                            menubar: 'file edit view insert format tools table help',
                            height: 400,
                        }
                    }
                ]
            } );
            
            var table_main = new DataTable('#prompts', {
                ajax: "./ajax.php",
                pageLength: 50,
                columns: [
                    { data: "prompts.objectif" },
                    { data: "prompts.statut", render: function (data, type, row) { return statut[data]; } },
                    { data: "prompts.niveau", render: function (data, type, row) { return niveau[data]; } },
                    { data: "prompts.user_owner", render: function (data, type, row) { return users[data]; } }
                ],
                layout: {
                    topStart: {
                        buttons: [
                            { extend: 'create', editor: editor_main },
                            { extend: 'edit', editor: editor_main },
                            { extend: 'remove', editor: editor_main }
                        ]
                    }
                },
                select: {
                    style: 'single'
                }
            } );
            table_main.on( 'select', function () {
                var selected = table_main.row( { selected: true } );
                // alert(selected.data().prompts.id);
                // console.log(selected.data());
                if (selected.any()) {
                    cur_prompt_id=selected.data().prompts.id;
                }
            } );
            
            editor_main.on('initEdit', function () {
                editor_main.field('prompts_prompts.prompt_id').show();
                    editor_main.field('prompts_prompts.prompt_id').dt().ajax.reload();
                editor_prompts.field('prompt_id').def(cur_prompt_id);
            });

            editor_main.on('initCreate', function () {
                editor_main.field('prompts_prompts.prompt_id').hide();
            });
        } );
    </script>
  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0 
    </head>
<body>
    <table id="prompts" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Objectifs</th>
                <th width="100">Statut</th>
                <th width="100">Niveau</th>
                <th width="200">Owner</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Objectifs</th>
                <th>Statut</th>
                <th>Niveau</th>
                <th>Owner</th>
            </tr>
        </tfoot>
    </table>
</body>
</html>

    I'm sure the issue came from me as your example is working, but I'm struggling to find it !

  • kthorngrenkthorngren Posts: 22,482Questions: 26Answers: 5,167

    In your #prompts Datatable config it looks like the buttons are using the wrong Editor instance of editor_main. I think it should be this:

                var table_main = new DataTable('#prompts', {
...
                layout: {
                    topStart: {
                        buttons: [
                            { extend: 'create', editor: editor_prompts },
                            { extend: 'edit', editor: editor_prompts },
                            { extend: 'remove', editor: editor_prompts }
                        ]
                    }
                },
...
            } );

    Kevin

  • kthorngrenkthorngren Posts: 22,482Questions: 26Answers: 5,167
    edited June 8

    I believe the problem is with the buttons config for the #prompts Datatable. They are referencing editor_,main but I believe they should reference editor_prompts. Like this:

                    layout: {
                    topStart: {
                        buttons: [
                            { extend: 'create', editor: editor_prompts },
                            { extend: 'edit', editor: editor_prompts },
                            { extend: 'remove', editor: editor_prompts }
                        ]
                    }
                },

    Kevin

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    Hi thorngen,

    Thanks for your feedback.

    Unfortunatly, this one are correct, the #prompts Datatable has to be editor_main.
    The editor_main is working correctly.

    It's the nested editor (editor_prompts) who create the error.
    I just saw an error in the button (thanks to you) in the nested editor, here the correct code :

                            label: "Liste des prompts :",
                        name: "prompts_prompts.prompt_id",
                        type: "datatable",
                        editor: editor_prompts,
                        submit: false,
                        optionsPair: {
                            value: 'prompts_prompts.id'
                        },
                        config: {
                            ajax: {
                                url: './ajax_prompts.php',
                                type: 'POST',
                                data: function (d) {
                                    d.prompt_id = cur_prompt_id;
                                }
                            },
                            buttons: [
                                { extend: "create", editor: editor_prompts, text: 'Ajout' },
                                { extend: "edit",   editor: editor_prompts, text: 'Modification' },
                                { extend: "remove", editor: editor_prompts, text: 'Suppression' },
                            ],
                            columns: [
                                {
                                    title: 'Prompt ID :',
                                    data: 'prompts_prompts.prompt_id'
                                },
                                {
                                    title: 'Titre :',
                                    data: 'prompts_prompts.titre'
                                },
                                {
                                    title: 'Prompt :',
                                    data: 'prompts_prompts.prompt'
                                }
                            ]
                        }

    But I still got the same javascript error...

  • IT CMSIT CMS Posts: 10Questions: 1Answers: 0

    I found the bug !!!!

    I define editor_prompts after editor_main !

    Thanks for your help :)

Sign In or Register to comment.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2025 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.