Datatable custom filtering with Server Side with Editor

shovon-choudhury

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"],
        ],
    });
});

shovon-choudhury

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

colin

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

koniahin

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.

colin

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

Colin

allan

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-choudhury

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

allan

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-choudhury

@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

colin

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-choudhury

@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

allan

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-choudhury

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

allan

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-choudhury

@allan Thank you so much. It worked!

shovon-choudhury

@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-choudhury

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-choudhury

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