How to integrate Select2 in table and edit menu?

tablo

Hi all,

I have hard time integrating Select2 both in the table as well as in the edit menu. I've read the manual and seen some examples but I'm either getting errors or there are errors but no dropdowns.

The response from server looks like this:

{
    "data": [{
            "DT_RowId": "row_name1",
            "name": "name1",
            "lines": null
        },
        {
            "DT_RowId": "row_name2",
            "name": "name2",
            "lines": null
        },
        {
            "DT_RowId": "name3",
            "name": "name3",
            "lines": {
                "results": [{
                        "id": 0,
                        "text": "text1"
                    },
                    {
                        "id": 1,
                        "text": "text2"
                    }
                ]
            }
        }
    ]
}

and this my script:

var editor;
$(document).ready(function () {
    editor = new $.fn.dataTable.Editor({
        ajax: {
            url: "/api/test1",
            dataSrc: "data"
        },
        table: "#example",
        fields: [{
            label: "Name:",
            name: "name",
            "type": "text"
        }, {
            label: "Lines:",
            name: "text",
            type: "select2",
            options: "data.lines",
        }]
    });

    var table = $('#example').DataTable({
        dom: "Bfrtip",
        ajax: {
            url: "/api/test1",
            dataSrc: "data"
        },
        columns: [{
            "data": 'name'
        }],
        buttons: [{
                extend: "create",
                editor: editor
            },
            {
                extend: "edit",
                editor: editor
            },
            {
                extend: "remove",
                editor: editor
            }
        ]
    });
});

What am I supposed to put in the "name" and "options"? I have tried different things but nothing has worked.
How can I check what data is coming from the ajax request?
Could someone please help with this one or provide a complete example?

allan

options: "data.lines",

That's not going to work. The options property for the Select2 field type should be an array of values (usually objects), not a string (it doesn't attempt to resolve that in the JSON).

Typically, you need the server to define the options, so often you wouldn't use the options property at all. Instead, in the Ajax response define an options object along with the field name they should apply to (allowing the options for more than one field to be sent) and the options themselves.

Have a look at this example and click on the "Ajax load" tab below the table. If you scroll right down the JSON you will see the options defined for users.site. The same method is used for the Select2 field type.

The reference documentation for that is available here.

Allan

tablo

@allan : Thanks for the quick response!
I've tried the example you mentioned:

var myoptions = {
    "users.site": [{
            "label": "Edinburgh",
            "value": "1"
        },
        {
            "label": "London",
            "value": "2"
        },
        {
            "label": "Los Angeles",
            "value": "6"
        },
        {
            "label": "New York",
            "value": "4"
        },
        {
            "label": "Paris",
            "value": "3"
        },
        {
            "label": "Singapore",
            "value": "5"
        }
    ]

};

editor.data().options = myoptions;

and I was able to see the dropdown menu in the edit menu if I put:

name: "users.site",

as editor property.

Not sure If understand you correctly but in my case each table cell can have or not a dropdown menu and if it has it is different from other table rows. In the example the dropdown is fixed and does not vary from row to row.

So what can I do in my case?

Otherwise I have done some progress in the table but there too I have two issues:
1. The dropdown menus appear only on the first page. From the second page on appear only icons (down arrow) without content.
2. On the first page I have two dropdown menus which appear correctly but the values in both of them are joined.

This is how the updated script looks like:

var editor;
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor({
        ajax: {
            url: "/api/test1",
            dataSrc: "data"
        },
        table: "#example",
        fields: [{
            label: "Name:",
            name: "name",
            "type": "text"
        }, {
            label: "Lines:",
            name: "data.lines",
            type: "select2",
            //options: data.lines.options,
            //other joined fields...
        }]
    });
    var col = [{
            data: "name"
        },
        {
            data: null,
            render: function(data, type, row, meta) {
                if (row.lines != null) {
                    return '<select class="select2">Lines</select>';
                } else
                    return '<td></td>';
            }
        }
    ];
    var table = $('#example').DataTable({
        dom: "Bfrtip",
        select: {
            style: 'single'
        },
        ajax: {
            url: "/api/test1",
            dataSrc: "data"
        },
        columns: col,
        initComplete: function(settings, json) {
            var myStringArray = json.data;
            var arrayLength = myStringArray.length;
            for (var i = 0; i < arrayLength; i++) {
                console.log(myStringArray[i].lines);
                if (myStringArray[i].lines != null) {
                    $(".select2").select2({
                        data: myStringArray[i].lines.options,
                        width: "200px"
                    })
                } else {
                    console.log("NULL");
                }
            }
        },
        buttons: [{
                extend: "create",
                editor: editor
            },
            {
                extend: "edit",
                editor: editor
            },
            {
                extend: "remove",
                editor: editor
            }
        ],
    });
});

BTW
Is not possible to edit / update posted posts?

tablo

Regarding this issue:
1. The dropdown menus appear only on the first page. From the second page on appear only icons (down arrow) without content.
It seems to be related to this:

https://datatables.net/faqs/index
Q. My events don't work on the second page

From there:
One of the best ways of dealing with this is through the use of delegated events with jQuery's on method

but how can I apply this method in the case of a Select2 dropdown?

allan

So what can I do in my case?

You need to use initEdit to make an Ajax call to get the options from the database and then the update method of the Select2 field type to set the options. e.g.

editor.on( 'initEdit', function ( e, node, data, items, type ) {
  $.getJSON( '/url', data, function ( json ) {
    editor.field('myField').update( json );
  } );
} );

Allan

tablo

I still can not make it work. I've used initEdit like this:

editor = new $.fn.dataTable.Editor({
    ajax: {
        url: "http://localhost:8081/api/test1",
        type: "POST",
        dataSrc: "options.cat1"
    },
    fields: [{
            label: "CAT11:",
            name: "cat11",
            "type": "text"
        },
        {
            label: "CAT13:",
            name: "cat13",
            type: "select2",
            attr: {
                multiple: "multiple"
            },
            optionsPair: {
                label: 'id',
                value: 'text'
            }
        }
    ]
});
    
editor.on('initEdit', function (e, node, data, items, type) {
        $.getJSON('http://localhost:8081/api/test1', data, function (json) {
            catArray = json.options.cat1;
            catArraylength = catArray.length;
            for (var i = 0; i < catArraylength; i++) {
                console.log(catArray[i].cat13);
                var optionArray = catArray[i].cat13;
                editor.field('cat13').update(x);
            }
        });
    });

As I've mentioned before each dropdown menu could be different for each cell. The "field.name" seems to be accepting only fixed values and when I put json in place of x then I get a dropdown menu with the options:
data, fieldErrors, files, options
When I put optionArray, which is an array with options in the Select2 data format, I get a dropdown menu with 0 and 1.

I've modified the json, which is being fetched via AJAX like this:

{
    "data": {
        "cat1": [
            {
                "cat11": "row1",
                "DT_RowId": "row_row1",
                "cat12": [
                    "text0",
                    "text2",
                    "text4"
                ]
            },
            {
                "cat11": "row2",
                "DT_RowId": "row_row2",
                "cat12": []
            },
            {
                "cat11": "row3",
                "DT_RowId": "row_row3",
                "cat12": [
                    "text0",
                    "text2"
                ]
            },
            {
                "cat11": "row4",
                "DT_RowId": "row_row4",
                "cat12": []
            }
        ]
    },
    "fieldErrors": [],
    "files": {},
    "options": {
        "cat1": [
            {
                "cat11": "row1",
                "DT_RowId": "row_row1",
                "cat13": [
                    {
                        "id": 0,
                        "text": "text0"
                    },
                    {
                        "id": 1,
                        "text": "text1"
                    },
                    {
                        "id": 2,
                        "text": "text2"
                    },
                    {
                        "id": 3,
                        "text": "text3"
                    },
                    {
                        "id": 4,
                        "text": "text4"
                    },
                    {
                        "id": 5,
                        "text": "text5"
                    },
                    {
                        "id": 6,
                        "text": "text6"
                    }
                ]
            },
            {
                "cat11": "row2",
                "DT_RowId": "row_row2",
                "cat13": []
            },
            {
                "cat11": "row3",
                "DT_RowId": "row_row3",
                "cat13": [
                    {
                        "id": 0,
                        "text": "text0"
                    },
                    {
                        "id": 1,
                        "text": "text1"
                    },
                    {
                        "id": 2,
                        "text": "text2"
                    }
                ]
            },
            {
                "cat11": "row4",
                "DT_RowId": "row_row4",
                "cat13": [
                    {
                        "id": 0,
                        "text": "text0"
                    },
                    {
                        "id": 1,
                        "text": "text1"
                    }
                ]
            }
        ]
    }
}

allan

I think your initEdit should look more like this:

editor.on('initEdit', function(e, node, data, items, type) {
    $.getJSON('http://localhost:8081/api/test1, data, function(json) {
        editor.field('cat13').update(json);
    });
});

That assumes that /api/test1 will check the data that was sent to it for the right row id, and get the list of options based on that id (since as you say, it varies by row).

Then return an array of id/text objects which will be passed to the field().update() function.

Allan

tablo

When I use initEdit the way you mentioned I get as options in the dropdown: data, fieldErrors,files,options.

When I use for and if like this

editor = new $.fn.dataTable.Editor({
    ajax: {
        url: "http://localhost:8081/api/test1",
        type: "POST",
        dataSrc: "options.cat1"
    },
    fields: [{
            label: "CAT11:",
            name: "cat11",
            "type": "text"
        },
        {
            label: "CAT13:",
            name: "cat13",
            type: "select2",
            attr: {
                multiple: "multiple"
            },
            optionsPair: {
                label: 'id',
                value: 'text'
            }
        }
    ]
});

editor.on('initEdit', function (e, node, data, items, type) {
    $.getJSON('http://localhost:8081/api/test1', data, function (json) {
        catArray = json.options.cat1;
        catArraylength = catArray.length;
        for (var i = 0; i < catArraylength; i++) {
            if (data.DT_RowId == catArray[i].DT_RowId) {
                editor.field("cat13").update(catArray[i].cat13);
            }
        }
    });
});

I get as options in the dropdown the correct id's (e.g. 0,1,..) for each row, and when I swap the label and value in the optionsPair like this:

optionsPair: {
    label: 'text',
    value: 'id'
}

I get the correct text (e.g. text0, text1, ...), which is actually what I want but now other issues appear, namely when I choose one of them and click update

1. the whole row disappears from the datatable and it appears when I reload the page
2. the wrong database column gets written, namely this one with the options not the selected values
3. the written values in the database column have this form:

{"0","1"}

(when I select the first two)

and the modified json looks like this:

{
    "cat11": "row4",
    "DT_RowId": "row_row4",
    "cat13": [
        {
            "id": 0,
            "text": "{\"0\",\"1\"}"
        }
    ]
}

When I swap the label and value in the optionsPair again it has this form:

{"text0","text1"}

allan

I might be misunderstanding your JSON format. Are you saying that you are loading all rows and all of their individual options? Not just the options for that specific row?

Is that information already available in the data you feed into the DataTable? If so, it would be available at row().data() so no need to make an Ajax call.

Allan