Here is a working example of using select2 dropdown

asle

After so much help I decided to write down a working example of using the **select2 library **dropdown as I hope this can be of help to others. I used the example for the "tags-multiple" as a base.

Here is the working example. I can not guarantee it will stay up forever on this server!

The reason I used select2 instead of the tags was because I already had a large database where the items are stored in a single field (no joins with another table) like this: "item1, item2, item3 etc..." so I wanted the fastest approach without rebuilding the DB structure.
In this example the I use the permissions table where all available items are for the dropdown choice(s)

Here is the setup:
In the main html include these files after jquery and Datatables css and javascript

    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    <!--  #### SELECT2 AUTOCOMPLETE #### -->
    <script src="/code/js/editor.select2.js"></script>

For the Editor code you set up and the select2 field in Editor like this:

var editor = new DataTable.Editor({
    ajax: '../../controllers/select2.php',
    fields: [
        {
            label: 'Permissions:',
            name: 'users.permission',
            type: 'select2',
            opts: {
                allowClear: true,
                multiple: true
            },
     ...and the rest of the fields....

For the controller (php) here is the permissions field. Reads the main table - users.permission and creates the dropdown from the permission.name field.

Field::inst( 'users.permission' )
            ->options(
                Options::inst()
                    ->table('permission')
                    ->value('name')
                    ->label('name')
            )
            ->setFormatter(function($val, $data, $opts) {
            return is_array($val) ? implode(', ', $val) : $val;
                })
            ->getFormatter(function($val, $data, $opts) {
                if (empty($val)) {
                    return [];
                }
                return array_filter(array_map('trim', explode(',', $val)));
            })
        ,

Now since this is an array it will list out the array items without spaces so I wrote this in the Datatable fields to make sure there is a space between several items :

    columns: [
        datafields here...... ,
        { data: 'users.permission',
            render: function(data, type, row) {
                if (Array.isArray(data)) {
                 return data.join(', ');
            }
            return data;
        } }

allan

Excellent - thank you for this! I'm sure others will appreciate it.

I already had a large database where the items are stored in a single field (no joins with another table)

Worth noting that tags could handle this. It just needs a SELECT DISTINCT to get the options (which the Options class could do).

Allan

asle

Thanks Allan. I would like an example (if you have time) using the tags field!

Already found a bug in my code :-( If I already have items in the "permissions" field I can not delete them all. It works if I have 1 item left but if I try to delete the only item It will not update. I can update the other records fields but the select2 field does not update.
You see it in the demo.

• The "permissions" field is empty and I edit other fields:

"data": [
        {
            "DT_RowId": "row_30",
            "users": {
                "first_name": "Dexter",
                "last_name": "Burtons",
                "sites": "Singapore",
                "permission": []
            }
        }
    ],

I try to delete the only item "Accounts" but it still sends the item

"data": [
        {
            "DT_RowId": "row_28",
            "users": {
                "first_name": "Basiali",
                "last_name": "Ryde von Harrell",
                "sites": "New York",
                "permission": [
                    "Accounts"
                ]
            }
        }
    ],

asle

If I remove multiple: true I can submit an empty "permissions" field. So this it seems something is not working with multiple selection.

asle

I tried the solution from here where I created a new record with id:10 and value null. Set my code like this but I still can not clear all items:

               opts: {
                placeholder: {
                    "id": "10",
                    "text":"Choose permissions..."
                        },
                allowClear: true,
                multiple: true,
                debug: true
            }

asle

It seems I finally got this to work. I have a preSubmit function that converts Select2 array to comma-separated string. Here is the function to be included. It is working now on my test page. I can now remove all entries in the "permission" field and update.

editor.on('preSubmit', function (e, data, action) {
    if (action === 'edit' || action === 'create') {
        // Get selected values from Select2 input (array or null)
        let selected = editor.field('users.permission').input().val();

        if (!selected) {
            selected = [];
        }

        if (!Array.isArray(selected)) {
            selected = [selected];
        }

        // Convert array to string for DB
        let selectedStr = selected.join(', ');

        // Set Editor field value to string before submit
        editor.set('users.permission', selectedStr);

        // Also update outgoing data object explicitly
        for (const key in data.data) {
            if (data.data.hasOwnProperty(key)) {
                data.data[key].users.permission = selectedStr;
            }
        }

        console.log('Submitting permissions string:', selectedStr);
    }
});

asle

If you have several select2 fields here is a a preSubmit function that can handle several fields. It may be too complicated but it does the job. Replace it with the first preSubmit function.

editor.on('preSubmit', function (e, data, action) {
    if (action === 'edit' || action === 'create') {
        // List of Select2 fields to process
        const fields = ['publikasjon.kommune', 'publikasjon.fylke'];

        fields.forEach(function (fieldName) {
            // Get array of selected values from input
            let selected = editor.field(fieldName).input().val();
            if (!selected) {
                selected = [];
            }
            if (!Array.isArray(selected)) {
                selected = [selected];
            }
            // Convert to comma-separated string
            const selectedStr = selected.join(', ');
            // Update Editor's field value
            editor.set(fieldName, selectedStr);
            // Update outgoing data manually
            for (const key in data.data) {
                if (data.data.hasOwnProperty(key)) {
                    if (!data.data[key].publikasjon) {
                        data.data[key].publikasjon = {};
                    }
                    data.data[key].publikasjon[fieldName.split('.')[1]] = selectedStr;
                }
            } // remove line below if you dont want console logging
            console.log(`Submitting ${fieldName}:`, selectedStr);
        });
    }
});

allan

Before I put a quick example together, I just want to check my understanding - you have a permission column in the database, which is a simple text field, and it contains comma separated values? There is no relationship between one and another, other than it being the same string value? If you were to delete an option ("VMs" for example) from all rows, then it would no longer show up in the Select2 list?

Actually, looking at the demo, that must be wrong since "Accounts" is shown as an option although it isn't used anywhere. How do you currently get the list of options?

Allan

asle

For the demo I create the Select2 list from the permission.name entries. The dropdown only shows those values. The user.permissions is a text field of comma separated values. If a value is not in the permission table it would show as a separate (existing) value but it would not be available from the dropdown if it is deleted since it is not in the permission table. So to your question, if "VM" is deleted from all records it would still show in the dropdown. I use this on another page where the list is municipals and cities. So I would have to add a new city to the city table to able to use it.

allan

The tags field has it's separator option which can be used to create a string from a list of options - in this case being comma separated, it would just be ,.

For populating the list of options, I tend to use a foreign key (join), as you alluded to above, which is good for referential integrity (e.g. if you wanted to change the spelling of one of the items), but there is no reason why the name couldn't be used as the value as well.

So for example, in this example, this is what is used:

                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    )

But it would be valid to use:

                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'name' )
                        ->label( 'name' )
                    )

Just make sure to have a constraint on name to not allow duplicates .

Then add separator: ',' to the client-side configuration for tags, and the job's done.

This is not to take away from your Select2 demo at all - I think it is excellent and I'm delighted there is a working demo for folks to refer to now - thank you! I just wanted to point out that this is possible with tags as well .

Allan

asle

Thank you Allan for your kind words! I would probably use the tags solution on a new project also avoiding the trouble when emptying a field already with content (having to add the presubmit function). Tags seems a cleaner solution for the task. But it was just more practical when I had thousands of records with content already filled in.