Datatable custom filtering with Server Side with Editor

Datatable custom filtering with Server Side with Editor

shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

Hi,
I am having some trouble integrating some custom functionalities with Datatable and Datatable Editor at the same time.
Without server-side processing my current functionalities works perfectly but I want to be able to implement server-side filtering with Editor.

With server-side filtering these work:
1.Pagination
2.Global Search
3.Sorting
4.Row reorder
5.Dynamic Page length

With server-side filtering these don't work:
1.Custom column input filtering
2.Custom footer select filtering

My serverside script for Datatable and Editor:

Editor::inst($db, 'article_categories')
            ->fields(
                Field::inst('article_categories.id')->validator('Validate::numeric'),
                Field::inst('article_categories.name')->validator('Validate::notEmpty'),
                Field::inst('article_categories.description'),
                Field::inst('article_categories.rowOrder')->validator('Validate::numeric')
            )
            ->on('preCreate', function ($editor, $values) {
                if (!$values['article_categories']['rowOrder']) {
                    $next = $editor->db()->sql('select IFNULL(MAX(rowOrder)+1, 1) as next FROM article_categories')->fetch();
                    $editor->field('article_categories.rowOrder')->setValue($next['next']);
                } else {
                    $editor->db()
                        ->query('update', 'article_categories')
                        ->set('rowOrder', 'rowOrder+1', false)
                        ->where('rowOrder', $values['article_categories']['rowOrder'], '>=')
                        ->exec();
                }
            })
            ->on('preRemove', function ($editor, $id, $values) {
                $order = $editor->db()
                    ->select('article_categories', 'rowOrder', array('id' => $id))
                    ->fetch();

                $editor->db()
                    ->query('update', 'article_categories')
                    ->set('rowOrder', 'rowOrder-1', false)
                    ->where('rowOrder', $order['rowOrder'], '>')
                    ->exec();
            })
            ->process($request->all())
            ->json();

My client-side script:

Default config:

jQuery(function () {
    $.extend(true, $.fn.dataTable.defaults, {
        serverSide: true,
        fixedHeader: true,
        searchDelay: 800,
        paging: true,
        processing: true,
        pageLength: 10,
        info: true,
        dom: "Blfrtip",
        select: true,
        responsive: true,
        lengthMenu: [
            [10, 25, 50, -1],
            [10, 25, 50, "All"],
        ],
    });
});

This question has accepted answers - jump to:

Answers

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    Datatable and Editor setup:

    var editor;
    jQuery(function () {
        //Editor
        editor = new $.fn.dataTable.Editor({
            table: "#article-category",
            ajax: {
                url: "article-categories",
                type: "POST",
            },
            fields: [
                {
                    label: "Order:",
                    name: "article_categories.rowOrder",
                    type: "hidden",
                    fieldInfo:
                        "This field can only be edited via click and drag row reordering.",
                },
                {
                    label: "FAQ Category Name:",
                    name: "article_categories.name",
                },
                {
                    label: "Description (optional):",
                    name: "article_categories.description",
                    type: "textarea",
                },
            ],
        });
    
        //Datatable
        var table = $("#article-category").DataTable({
            ajax: {
                url: "article-categories",
                type: "POST",
            },
            rowReorder: {
                dataSrc: "article_categories.rowOrder",
                editor: editor,
            },
            buttons: cms.editorFormButtons(editor),
            initComplete: function () {
                cms.headerInputFilter("article-category", this.api(), [1, 2]);
                cms.footerSelectFilter(this.api(), [1, 2]);
            },
            columns: [
                {
                    data: "article_categories.rowOrder",
                    name: "article_categories.rowOrder",
                    className: "reorder no-inline",
                },
                {
                    data: "article_categories.name",
                    name: "article_categories.name",
                },
                {
                    data: "article_categories.description",
                    name: "article_categories.description",
                },
            ],
        });
    
        //Inline Editor
        cms.inlineEdit("article-category", editor);
    
        editor
            .on("postCreate postRemove", function () {
                table.ajax.reload(null, false);
            })
            .on("initCreate", function () {
                editor.field("article_categories.rowOrder").enable();
            })
            .on("initEdit", function () {
                editor.field("article_categories.rowOrder").disable();
            });
    });
    
    

    Custom functions:

    let footerSelectFilter = function (table, columns) {
        if (typeof columns != "undefined" && typeof table != "undefined") {
            table.columns(columns).every(function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo($(column.footer()).empty())
                    .on("change", function () {
                        var val = $.fn.dataTable.util.escapeRegex($(this).val());
    
                        column
                            .search(val ? "^" + val + "$" : "", true, false)
                            .draw();
                    });
    
                column
                    .data()
                    .unique()
                    .sort()
                    .each(function (d, j) {
                        select.append(
                            '<option value="' + d + '">' + d + "</option>"
                        );
                    });
            });
        }
    };
    
    let headerInputFilter = function (target, table, searchableColumns) {
        if (
            typeof searchableColumns != "undefined" &&
            typeof target != "undefined" &&
            typeof table != "undefined"
        ) {
            $("#" + target + " thead tr")
                .clone(true)
                .addClass("filters")
                .appendTo("#" + target + " thead");
    
            var i = 0;
            var api = table;
            api.columns()
                .eq(0)
                .each(function (colIdx) {
                    if (
                        searchableColumns.includes(
                            $(api.column(colIdx).header()).index()
                        )
                    ) {
                        var cell = $(".filters th").eq(
                            $(api.column(colIdx).header()).index()
                        );
                        var title = $(cell).text();
                        $(cell).html(
                            '<input style="width:100% !important" type="text" placeholder="' +
                                title +
                                '" />'
                        );
                        $(
                            "input",
                            $(".filters th").eq(
                                $(api.column(colIdx).header()).index()
                            )
                        )
                            .off("keyup change")
                            .on("keyup change", function (e) {
                                e.stopPropagation();
                                $(this).attr("title", $(this).val());
                                var regexr = "({search})";
    
                                var cursorPosition = this.selectionStart;
                                api.column(colIdx)
                                    .search(
                                        this.value != ""
                                            ? regexr.replace(
                                                  "{search}",
                                                  "(((" + this.value + ")))"
                                              )
                                            : "",
                                        this.value != "",
                                        this.value == ""
                                    )
                                    .draw();
    
                                $(this)
                                    .focus()[0]
                                    .setSelectionRange(
                                        cursorPosition,
                                        cursorPosition
                                    );
                            });
                    } else {
                        return true;
                    }
                });
        }
    };
    
    let editorFormButtons = function (editor) {
        if (typeof editor != "undefined") {
            return [
                {
                    extend: "create",
                    editor: editor,
                    formButtons: [
                        {
                            label: "Save",
                            fn: function () {
                                var that = this;
                                this.submit(function () {
                                    that.close();
                                });
                            },
                        },
                    ],
                },
    
                {
                    extend: "edit",
                    text: "Edit",
                    editor: editor,
                    formButtons: [
                        {
                            label: "Save & close",
                            fn: function () {
                                var that = this;
                                this.submit(function () {
                                    that.close();
                                });
                            },
                        },
                        {
                            label: "Update",
                            fn: function () {
                                this.submit(function () {
                                    editor.edit(
                                        table.rows({ selected: true }).indexes()
                                    );
                                });
                            },
                        },
                    ],
                },
    
                {
                    extend: "remove",
                    editor: editor,
                    formButtons: [
                        {
                            label: "Delete",
                            fn: function () {
                                var that = this;
                                this.submit(function () {
                                    that.close();
                                });
                            },
                        },
                    ],
                },
            ];
        }
    };
    
    let inlineEdit = function (target, editor) {
        if (typeof target != "undefined" && typeof editor != "undefined") {
            $("#" + target + "").on(
                "click",
                "tbody td:not(.no-inline)",
                function (e) {
                    if (
                        $(this).hasClass("editor-edit") ||
                        $(this).hasClass("control") ||
                        $(this).hasClass("select-checkbox") ||
                        $(this).hasClass("dataTables_empty")
                    ) {
                        return;
                    }
                    editor.inline(this, {
                        submit: "allIfChanged",
                    });
                }
            );
        }
    };
    
    module.exports = {
        headerInputFilter,
        footerSelectFilter,
        editorFormButtons,
        inlineEdit,
    };
    

    When I set server-side to false the custom functionalities I need works perfectly but I need these with server-side processing as it will significantly improve overall performance . I would appreciate any help and suggestion.
    Regards,
    Shovon Choudhury

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Thanks,

    Colin

  • koniahinkoniahin Posts: 186Questions: 39Answers: 7

    Colin, Just to let you know Shovon is doing this work for me under my account/license. I am not skilled with JavaScript and commonly call on him to help. My live installation is a custom CMS I developed. We're attempting to transition it to Laravel framework. If necessary I can repost this under my account, however it would be easier to communicate back/forth with him due to my lack of skills.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Ah, thanks for letting us know, I've updated Shovon's account with that info. We'll take a look and report back,

    Colin

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    I suspect the regex part of the column filtering is what is causing the problem here.

    The server-side processing implementations we ship don't include support for regex filtering on the database, as it can be a real performance drag.

    Could you try using:

                        column
                            .search(val")
                            .draw();
    

    please?

    If it isn't that, are you able to give me a link to your page so I can take a look?

    Thanks,
    Allan

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0
    edited January 2022

    Thanks @allan , It resolved the issue. We need some more help regarding inline editing and keeping main pop up editor open when "Update" is clicked. We must mention we are using bs4 dist.

    1. With inline editing when we update the data and click another record the record get updated but there's an error in the console "Uncaught TypeError: g is undefined"
    $("#" + target + "").on("click", "tbody tr td", function (e) {
                if (
                    $(this).hasClass("editor-edit") ||
                    $(this).hasClass("control") ||
                    $(this).hasClass("select-checkbox") ||
                    $(this).hasClass("dataTables_empty") ||
                    $(this).hasClass("no-online")
                ) {
                    return;
                }
                editor.inline(this, {
                    onBlur: "submit",
                    submit: "changed",
                });
            });
    

    We are using a temporary fix which we don't exactly want:

    editor.inline(this, {
                    buttons: "_basic",
                    submit: "changed",
            });
    
    1. When we are editing a record in popup windows we need to be able to keep the form open when we click on "Update". Here is our approach:
    {
                extend: "edit",
                text: "Edit",
                editor: editor,
                formButtons: [
                    {
                        label: "Save & close",
                        fn: function () {
                            var that = this;
                            this.submit(function () {
                                that.close();
                            });
                        },
                    },
                    {
                        label: "Update",
                        fn: function () {
                            this.submit(function () {
                                editor.edit(
                                    // We need the help to pass submitted reference to reopen new popup to avoid conflicts.
                                );
                            });
                        },
                    },
                ],
            },
    

    I have tried the following option it works but other buttons, selection start to malfunction sometimes clicking do nothing:

    formOptions: {
                        main: {
                            onComplete: 'none'
                        }
                    },
    

    Regards,
    Shovon Choudhury

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    Hi Shovon,

    The approach you've taken with the buttons looks correct. Where you have the comment:

    // We need the help to pass submitted reference to reopen new popup to avoid conflicts.

    This is where you would to Editor what row it should edit next. Do you want it to edit the same row again, or some other row? If some other, based on what logic?

    Allan

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    @allan we need to be able to edit the existing one.
    Steps:
    1. We edit and submit data
    2. Data should be stored in the DB
    3. Then the editor should open up with existing id with newly updated data.

    Please look into the inline editor issue as well. It happens when I inline edit something and on onBlur submit if click any other record during submission (quick select) it shows an error in console I mentioned earlier.
    Regards,
    Shovon Choudhury

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi Shovon,

    I believe this is doing what you're looking for. After the submission is complete (submitComplete) the row is re-edited. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

    Cheers,

    Colin

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0
    edited January 2022

    @colin Thanks for your reply. Unfortunately it's not populating old data with fields after the original one is submitted.

    editor.on("submitComplete", function (e, json, data, action) {
            if (action === "edit") {
                console.log(data.DT_RowId);
                editor.edit(data.DT_RowId);
            } else {
                editor.close();
            }
        });
    

    I have added a line to see if that if block is really working. It prints something like "row_1".

    As I mentioned previously, we are using datatables.net-bs4 dist. I hope that doesn't make any issue.
    Looking forward for your help.
    Regards,
    Shovon Choudhury

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    Hi Shovon,

    table.rows({selected: true}).indexes()
    

    Will give you the currently selected row index. You can pass that into the edit() method to trigger editing on the currently selected row.

    Allan

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    @allan I tried this previously but modifying it a bit it worked. We are still having trouble with inline editor.
    When we try editing a cell through inline editor with onBlur:"submit" it submits the record but if I click on another record during blur effect I get console log with type error "g is undefined".
    I have tried:

    editor.inline(this);
    

    This won't work without enter. Need your help.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    Hi Shovon,

    Using this (being a td) with server-side processing won't work. You need to use the cell index - see this example for how to do that.

    If that doesn't do the job (it should :)) can you give me a link to a page showing the issue so I can debug it please.

    Thanks,
    Allan

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    @allan Thank you so much. It worked!

  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    @allan now the rowreorder isn't reordering properly. I am implementing it with serverside processing. What am I doing wrong here?

    rowReorder: {
                dataSrc: "article_categories.rowOrder",
                selector: ".reorder",
                editor: editor,
            },
    select: {
                style: "os",
                selector: "td:first-child",
            },
    columns: [
                {
                    data: "article_categories.id",
                    name: "article_categories.id",
                    className: "no-online",
                },
                {
                    data: "article_categories.name",
                    name: "article_categories.name",
                },
                {
                    data: "article_categories.description",
                    name: "article_categories.description",
                },
                {
                    data: "article_categories.rowOrder",
                    name: "article_categories.rowOrder",
                    className: "reorder no-online",
                },
            ],
    
  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    Here is the serverside setup:

    Editor::inst($db, 'article_categories')
                ->fields(
                    Field::inst('article_categories.id')->validator('Validate::numeric'),
                    Field::inst('article_categories.name')->validator('Validate::notEmpty'),
                    Field::inst('article_categories.description'),
                    Field::inst('article_categories.rowOrder')->validator('Validate::numeric')
                )
                ->on('preCreate', function ($editor, $values) {
                    if (!$values['article_categories']['rowOrder']) {
                        $next = $editor->db()->sql('select IFNULL(MAX(rowOrder)+1, 1) as next FROM article_categories')->fetch();
                        $editor->field('article_categories.rowOrder')->setValue($next['next']);
                    } else {
                        $editor->db()
                            ->query('update', 'article_categories')
                            ->set('rowOrder', 'rowOrder+1', false)
                            ->where('rowOrder', $values['article_categories']['rowOrder'], '>=')
                            ->exec();
                    }
                })
                ->on('preRemove', function ($editor, $id, $values) {
                    $order = $editor->db()
                        ->select('article_categories', 'rowOrder', array('id' => $id))
                        ->fetch();
    
                    $editor->db()
                        ->query('update', 'article_categories')
                        ->set('rowOrder', 'rowOrder-1', false)
                        ->where('rowOrder', $order['rowOrder'], '>')
                        ->exec();
                })
                ->process($request->all())
                ->json();
    
  • shovon-choudhuryshovon-choudhury Posts: 15Questions: 3Answers: 0

    @allan I got it resolved. I had the wrong ordering. I just added the order option and it worked like a charm.
    Thanks a lot for your help.

Sign In or Register to comment.