Select2 with large data set

svendber

Hi,
I'm trying to use the Select2 plugin, but I'm facing some issues. I'm trying to use it with a large data set, 35.000+, however it's buggy and gives the Script timeout error. I do also face the following problems:

The field in the editor form is gone/blank. You can however click on it and the field do work, but still buggy as told.
I'm also trying to paginate it, however it doesn't work on the field. If I use the same code on a field not associated with Datatables, then the pagination works.

I've read more or less every page in this forum about Select2, and followed the documentation in https://editor.datatables.net/plug-ins/field-type/editor.select2 but couldn't find a solution. In this thread https://datatables.net/forums/discussion/54967 from a month ago, describes some of the issues regarding Select2.

I'm using Editor-PHP 1.9.0 and Datatables 1.10.18.

Below is my code, and I'm not sure how to proceed from here.

let editor = $(document).ready(function () {
        editor = new $.fn.dataTable.Editor({
            ajax: {
                create: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/create.php'
                },
                remove: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/remove.php?id=_id_'
                },
                edit: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/edit.php?id=_id_'
                }
            },
            table: "#intranet",
            fields: [
                {
                    "label": "Kundenummer:",
                    "name": "crapper_customer_intranet.kundenr"
                },
                {
                    "label": "Navn:",
                    "name": "crapper_customer_intranet.navn",
                    type: 'select2',
                    "opts": {
                        "ajax": {
                            url: "../backend/dependentTest.php",
                            dataType: 'json',
                            data: function (params) {
                                return {
                                    param: params.term,
                                    page: params.page || 1,
                                    rows: 10
                                };

                            },
                            processResults: function (data, params) {
                                console.log(data.data);
                                params.page = params.page || 1;
                                var listTR =  Array();

                                data.data.forEach(function(dataTR, i) {
                                    listTR[i] = {
                                        "text": dataTR.navn,
                                        "id": dataTR.kundenr
                                    };
                                });
                                return {
                                    results: listTR,
                                    pagination: {
                                        more: params.page < data.total
                                    }
                                };
                            },
                            minimumInputLength: 5,
                            maximum: 10,

                            delay: 500,
                            maximumSelectionLength: 5,
                            minimumResultsForSearch: -1,
                        },
                        "theme": "bootstrap",
                        "multiple":false,
                    }
                },

allan

To confirm - is the problem that the initial value when you click edit for a row is not shown? Or have I misunderstood?

Does your dependentTest.php script specifically handle the initialValue request that the Select2 integration for Editor makes?

Thanks,
Allan

svendber

hi @allan

Nevermind the field, I fixed it.

I've changed my code according to the documentation and with other options.

let editor = $(document).ready(function () {
        editor = new $.fn.dataTable.Editor({
            ajax: {
                create: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/create.php'
                },
                remove: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/remove.php?id=_id_'
                },
                edit: {
                    type: 'POST',
                    url: '../backend/backendCRUD/intranetCRUD/edit.php?id=_id_'
                }
            },
            table: "#intranet",
            fields: [
                {
                    "label": "Kundenummer:",
                    "name": "crapper_customer_intranet.kundenr"
                },
                {
                    "label": "Navn:",
                    "name": "crapper_customer_intranet.navn",
                    type: 'select2',
                    "opts": {
                        initialValue: true,
                        value: "",
                        "ajax": {
                            url: "../backend/dependentTest.php",
                            dataType: 'json',
                            delay: 250,              
                            data: function (params) {
                                return {
                                    query: params.term,
                                    page: params.page,
                            
                                };

                            },
                            processResults: function (data, params) {
                                console.log(data);
                                params.page = params.page || 1;
                                return {
                                    results: data,
                                    pagination: {
                                        more: (params.page * 30) < data.total_count
                                    }
                                };
                            },
                            cache: true
                        },
                        escapeMarkup: function (markup) {
                            return markup;
                        }, // let our custom formatter work
                        minimumInputLength: 3,
                        maximum: 10,
                        maximumSelectionLength: 5,
                        minimumResultsForSearch: -1, 
                        allowClear: true,

                    }
                },

I'm testing with the following serverScript:

<?php
include(dirname(__FILE__)."/../Editor-PHP-1.9.0/lib/DataTables.php");

// Alias Editor classes so they are easy to use
use DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Validate,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\ValidateOptions;
$editor = Editor::inst($db, 'crapper_customer_all')

    ->fields(
        Field::inst( 'id' ),
        Field::inst( 'text' )
    )
    ->process($_POST)
    ->debug(true)
    ->json();

It does make the ajax call but the select2 field keeps being empty.
The data returned from the server:

{"data":[{"DT_RowId":"row_1001","id":1001,"text":"Intern tid"},{"DT_RowId":"row_1002","id":1002,"text":"BDO Kommunernes Revision"},{"DT_RowId":"row_1112","id":1112,"text":"Kursus diverse debitorer"},{"DT_RowId":"row_2000","id":2000,"text":"Kopi - kunde"},{"DT_RowId":"row_2010","id":2010,"text":"Aller\u00f8d Kommune"},{"DT_RowId":"row_2100","id":2100,"text":"Fredensborg Kommune"}, 
.........

If I change the return value in ProcessResults

from

                                return {
                                    results: data,
                                    pagination: {
                                        more: (params.page * 30) < data.total_count
                                    }
                                };
                         

to

                                return {
                                    results: data.data,
                                    pagination: {
                                        more: (params.page * 30) < data.total_count
                                    }
                                };
                         

Then it works! However it still is really buggy and gives the ScriptTimeout error and stops the page from loading..

As you asked my server script doesn't handle the InitialValue request. I've searched throughout the forum to find a solution since I'm not sure how to handle the request.. The request is the following:

dependentTest.php?query=something

I'm guessing the problem is my server script...

Ty in advance!

// Svendber

allan

The issue here is that the Editor PHP libraries don't attempt to handle the Select2 Ajax requests at all. In future I might expand them to do so, so we can more tightly align with Select2 (as you are seeing its not trivial to get it working atm), but just now you need to make your own PHP script that will answer the Select2 requests for data, just as you would if you weren't using Editor.

Allan