Select2 with large data set

Select2 with large data set

svendbersvendber Posts: 27Questions: 8Answers: 0

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,
                    }
                },

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    edited November 2019

    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

  • svendbersvendber Posts: 27Questions: 8Answers: 0

    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

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Answer ✓

    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

This discussion has been closed.