How to deal with missing data on opening editor with server-side processing?

ComGreed

Over the past 6 month my table grew to a significant size. That's why I started to rewrite most of the code I use so it works with server-side processing now. The last missing piece is how to deal with selections of multiple rows and edit them because when you scroll past a certain point the extension Select forgets what was selected. As a workaround it now stores the row_ID in the variable selected. When I click the Test button it I want it to fetch the missing data from the server and use it to populate the Editor.

I searched for a long time now but could neither find a way fetch specific rows from the Datatables PHP library nor a way to feed custom data to the editor in the "correct" way.

I'd really appreciate any hint to what I could do in my case.

<script>
    let editor;
    let selected = [];

    $(document).ready(function () {
        let editor = new $.fn.dataTable.Editor({
            ajax: {
                create: {type: 'POST', url: '/test'},
                edit: {type: 'PUT', url: '/test/_id_'},
                remove: {type: 'DELETE', url: '/test/_id_', deleteBody: false}
            },
            formOptions: {main: {submit: 'changed'}},
            table: "#table",
            fields: [
                {label: "Test 1", name: "test1"},
                {label: "Test 2", name: "test2"},
                {label: "Test 2", name: "test3"}
            ]
        });

        let table = $('#table').DataTable({
            ajax: {
                url: '/test',
                type: 'POST'
            },
            serverSide: true,
            deferRender: true,
            scrollY: 'calc(100vh - 180px)',
            scroller: true,
            order: [[1, 'asc']],
            columns: [
                {
                    targets: 0, data: null, defaultContent: '',
                    orderable: false, searchable: false,
                    render: function ( data, type, row ) {
                        if ( type === 'display' ) {
                            return '<input type="checkbox" class="editor-active">';
                        }
                        return data;
                    },
                    className: "dt-body-center"
                },
                {
                    data: 'test1',
                    title: 'Test 1'
                },
                {
                    data: 'test2',
                    title: 'Test 2'
                },
                {
                    data: 'test3',
                    title: 'Test 3'
                },
            ],
            buttons: [
                {
                    text: 'Test',
                    action: function (e, dt, node, config) {
                        // TODO
                        let ajax_url = dt.ajax.url();
                        let ajax_columns = dt.ajax.params().columns;
                        let ajax_search = dt.ajax.params().search;
                        console.log(ajax_columns, ajax_search);
                        //let data = dt.column(1).search(JSON.stringify(selected).data();
                        //console.log(this.search(selected.map(i => '#' + i)).draw());
                        //let test = editor.edit(selected.map(i => '#' + i));
                        table.rows(selected).editor(); // only works if data is still present in the table
                    }
                }
            ],
            rowCallback: function(row, data) {
                if ($.inArray(data.DT_RowId, selected) !== -1)
                    $(row).find('input[type="checkbox"]').prop('checked', true);
            }
        }).on('click', 'input[type="checkbox"]', function(e){
            let that = this;
            let rowId = table.row($(that).closest('tr')).data().DT_RowId;
            let index = $.inArray(rowId, selected);
            if (that.checked && index === -1)
                selected.push(rowId);
            else if (!that.checked && index !== -1)
                selected.splice(index, 1);
            console.log(selected);
            e.stopPropagation();
        }).on('click', 'tbody td, thead th:first-child', function(e){
            $(this).parent().find('input[type="checkbox"]').trigger('click');
        });
    });
</script>
<div class="px-1 py-1 bg-faded">
    <table class="display compact nowrap" id="table" style="width:100%">
        <thead><tr><th></th><th></th><th></th><th></th></tr></thead>
        <tfoot><tr><th></th><th></th><th></th><th></th></tr></tfoot>
    </table>
</div>

rf1234

You say the "Select" extension "forgets" what was selected on some occasions. I have never experienced that to be honest.

But if you have the id's of the rows you meant to select you can use something like this to make sure all of your relevant rows are selected.

This code is from a custom button definition. I use this button to select all rows that should feed data into a certain interface so that the user can edit all of them on button click and is not forced to tediously select them manually. This logic should be relevant for your use case as well. You can take your list of row Ids and select them again for editing.

//custom button to select all rows that are in the accounting interface
$.fn.dataTable.ext.buttons.selectAllInterface = {
    text: "myLabelText",
    name: "selectedAllInterfaceButton",
    action: function ( e, dt, button, config ) {
        var sRows = dt.rows({ search: 'applied' }).data().toArray();
        var interfaceArray = [];
        var notInterfaceArray = [];
        $.each(sRows, function(key, value) {
            if ( value.myTable.include > 0 ) {
                interfaceArray.push("#" + value.DT_RowId);
            } else {
                notInterfaceArray.push("#" + value.DT_RowId);
            }
        })
        dt.rows(interfaceArray).select();
        dt.rows(notInterfaceArray).deselect();
    }
};

You want to make sure you "select" or "deselect" with an entire array of data. Don't do it individually for every row. It will cause performance issues. So I also tried this here - and it was too slow with a large amount of data.

//custom button to select all rows that are in the accounting interface using "Every"
$.fn.dataTable.ext.buttons.selectAllInterfaceEvery = {
    text: "myLabelText", + '_Every',
    name: "selectedAllInterfaceButton",
    action: function ( e, dt, button, config ) {
        dt.rows({ search: 'applied' }).every( function ( rowIdx, tableLoop, rowLoop ) {
            var data = this.data();
            if ( data.myTable.include > 0 ) {
                dt.row(this).select();
            } else {
                dt.row(this).deselect();
            }
        });
    }
};

ComGreed

I'm sorry I didn't make the situation clear enough in the first post.

Situation before switching to server-side processing:
The GET request pulls the complete database from the server and loads everything into datatabes. I am able to use the Select extension to select the rows I want to edit and then edit them using the Datatables Editor.

Situation after switching to server-side processing:
Datatables loads the data in small chunks from the server. I select the rows I want to edit as usual and scroll down (Scroller extension). After scrolling past a certain point the old selections get removed. This is a well-known issue and that's why I switched from Select to simple checkboxes that save the rowIDs into an array. I can use the following code without any problems as long as the data is still visible in the table and I didn't scroll down too much.

let test = editor.edit(selected.map(i => '#' + i));
table.rows(selected).editor();

It seems like when scrolling not only do the rows that are no more visible get removed from the table (which makes sense) but their corresponding data as well. That's why the editor still works as long as the data didn't get removed. When the data is missing it doesn't make a new request to fetch it from the server again.

This is why I'm asking the questions:
1. How do I form an ajax request to get data for a selection of rowIDs from Datatables PHP lib and
2. How do I feed this data to the Datatables Editor?

ComGreed

Okay, I finally solved the problem myself. It's really frustrating because I tried different stuff for more than 10 hours until I finally found the egg of Columbus. So for anyone that is interested in my solution.

Make sure that you save the complete data of the row upon ticking the checkbox. All that's left is to reinsert the data into the table when you click the button and open the editor using the rowids of the stored data with the following code.

table.rows.add(selected);
editor.edit(selected.getColumn('DT_RowId').map(i => '#' + i));

allan

The last missing piece is how to deal with selections of multiple rows and edit them because when you scroll past a certain point the extension Select forgets what was selected

Its worth circling back to this point in case anyone else finds this post (great to hear you've got a solution - sorry I wasn't able to help over the weekend).

With server-side processing you can only select rows which are currently drawn on the page. The row selection is client-side, so you can't select rows which don't exist on the client-side. For that reason you also can't edit them (unless you keep some kind of data map as you have done).

The way I normally suggest to address this is to keep the row selection store at the server-side (not the client). That means for every row select and deselect you have to tell the server about it, so it can store that information in session (or whatever) storage. Then you trigger an edit action and need to get the list of rows to be edited.

That is the downside of server-side processing - not all the data you want or need can be available are the client-side.

Allan