Select2 Field Not Properly Submitting to Server

Select2 Field Not Properly Submitting to Server

mikedmasonmikedmason Posts: 39Questions: 12Answers: 0

Hello,
I have recently implemented the NodeJS server side form of DT and Editor.
I wanted to implement the Select2 Plugin on a field to be able to assign multiple groups to a role essentially.

However when selecting more than one value, and clicking update, the field will either disappear or, still display the original value.
When looking at the database, no values have been changed, still the original value.

Here is the Front End;

jQuery(document).ready(function(){
            editor = new jQuery.fn.dataTable.Editor({
                "scrollX": true,
                dom: "Bfrtip",
                'processing': true,
                'serverSide': true,
                ajax: {
                    "type":"POST",
                    "url": api.URL + "/dtapi/access",
                },
                table: "#access_settings",
                fields: [
                {
                    label: "Page Name",
                    name: "access_settings_table.name"
                },
                {
                    label: "Allowed Groups",
                    name: "access_settings_table.groups",
                    type: "select2",
                    opts:{
                        "multiple": true
                    },
                    seperator: ";"
                }]
            })

            editor.on( 'submitComplete', function () {
                var table = jQuery('#access_settings').DataTable()

               table.ajax.reload();
            } );

            
            jQuery('#access_settings').DataTable({
                
                "scrollX": true,
                dom: "Blfrtip",
                'paging': true,
                'processing': true,
                'serverSide': true,
                "ajax": {
                    "url": api.URL + "/dtapi/access", //API
                    "type": "POST"
                },
                fields:[
                    {
                        "label": "id",
                        "name": "access_settings_table.id"
                    },
                    {
                        "label": "name",
                        "name": "access_settings_table.name"
                    },
                    {
                        "label": "groups",
                        "name": "access_settings_table.groups",
                        "type": "select2",
                        opts:{
                            "multiple": true

                        },
                        seperator: ";"

                    },
                ],
                "columns": [
                    { "data": "access_settings_table.id"},
                    { "data": "access_settings_table.name"},
                    { 
                        "data": "access_settings_table.groups",
                        "render": function(data, type, row, meta){
                            return data
                        },
                        "editField": "access_settings_table.groups"
                    },
                ],
                select: true,
                buttons: [
                    {
                        extend: "create", 
                        editor: editor
                    },
                    {
                        extend: "edit", 
                        editor: editor
                    },
                    {
                        extend: "remove", 
                        editor: editor
                    }
                ]
            })

        });

The Server Side NodeJS script prints out

{ action: 'edit',
  data: { row_0: { access_settings_table: [Object] } } }
{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''CUSTOMER', 'CUSTOMER SERVICE' where `id` = '0'' at line 1
    at Query.Sequence._packetToError (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:278:23)
    at Parser.write (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Parser.js:76:12)
    at Protocol.write (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:502:10)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
    --------------------
    at Protocol._enqueue (/home/kwla/master-kwla/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/kwla/master-kwla/node_modules/mysql/lib/Connection.js:200:25)
    at /home/kwla/master-kwla/node_modules/knex/lib/dialects/mysql/index.js:144:18
    at Promise._execute (/home/kwla/master-kwla/node_modules/bluebird/js/release/debuggability.js:313:9)
    at Promise._resolveFromExecutor (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:483:18)
    at new Promise (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:79:10)
    at Client_MySQL._query (/home/kwla/master-kwla/node_modules/knex/lib/dialects/mysql/index.js:135:12)
    at Client_MySQL.query (/home/kwla/master-kwla/node_modules/knex/lib/client.js:192:17)
    at Runner.<anonymous> (/home/kwla/master-kwla/node_modules/knex/lib/runner.js:138:36)
    at Runner.tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
    at Runner.query (/home/kwla/master-kwla/node_modules/bluebird/js/release/method.js:15:34)
    at /home/kwla/master-kwla/node_modules/knex/lib/runner.js:47:21
    at tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
    at /home/kwla/master-kwla/node_modules/bluebird/js/release/using.js:185:26
    at tryCatcher (/home/kwla/master-kwla/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/home/kwla/master-kwla/node_modules/bluebird/js/release/promise.js:512:31)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage:
   'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'\'CUSTOMER\', \'CUSTOMER SERVICE\' where `id` = \'0\'\' at line 1',
  sqlState: '42000',
  index: 0,
  sql:
   'update `access_settings_table` set `name` = \'admin_panel\', `groups` = \'ADMIN\', \'CUSTOMER\', \'CUSTOMER SERVICE\' where `id` = \'0\'' }
{ data: [], fieldErrors: [] }
{}
{ data:
   [ { DT_RowId: 'row_0', access_settings_table: [Object] },
     { DT_RowId: 'row_1', access_settings_table: [Object] },
     { DT_RowId: 'row_2', access_settings_table: [Object] },
     { DT_RowId: 'row_3', access_settings_table: [Object] },
     { DT_RowId: 'row_4', access_settings_table: [Object] },
     { DT_RowId: 'row_5', access_settings_table: [Object] },
     { DT_RowId: 'row_100', access_settings_table: [Object] },
     { DT_RowId: 'row_101', access_settings_table: [Object] },
     { DT_RowId: 'row_102', access_settings_table: [Object] },
     { DT_RowId: 'row_103', access_settings_table: [Object] },
     { DT_RowId: 'row_104', access_settings_table: [Object] },
     { DT_RowId: 'row_200', access_settings_table: [Object] },
     { DT_RowId: 'row_201', access_settings_table: [Object] },
     { DT_RowId: 'row_202', access_settings_table: [Object] } ],
  fieldErrors: [],
  draw: undefined,
  files: {},
  options:
   { 'access_settings_table.groups': [ [Object], [Object], [Object], [Object], [Object] ] },
  recordsTotal: undefined,
  recordsFiltered: undefined }

The route for this part of the server side script, looks as such;

dtapi.post('/access', async function(req, res, next){
    var request_package = req.body.request_package

    console.log(req.body)


    let editor = new Editor(mysql_db, 'access_settings_table')
    .fields(
        new Field('access_settings_table.id'),
        new Field('access_settings_table.name'),
        new Field('access_settings_table.groups')
        .options(new Options()
            .table('user_groups_table')
            .value('group_name')
            .label(['group_name'])
        
        
        )
    )

    await editor.process(req.body)
    .catch(function(err){
        console.log(err)
    })
    console.log(editor.data())
    res.json(editor.data());

})

Any tips to debug this would be much appreciated.

Answers

  • mikedmasonmikedmason Posts: 39Questions: 12Answers: 0

    I am seeing that the MySQL statement that Server Side editor is trying to print groups = ADMIN, CUSTOMER, CUSTOMER SERVICE rather than wrapping the individual items in single string.

  • mikedmasonmikedmason Posts: 39Questions: 12Answers: 0

    I have added a preSumbit function which now allows me to pass the data to the Server Side and actually submit to the DB. However Select2, is not able to associated those values with the selectable groups.

    preSubmit Function;

    editor.on( 'preSubmit', function ( e, data, action ) {
                    var cluster_values = ""
                    jQuery.each( data.data, function ( key, values ) {
                        var groups_array = data.data[key]['access_settings_table'].groups
                        
                        Object.keys(groups_array).forEach(function(key){
                            console.log(cluster_values)
                            cluster_values += (groups_array[key] + ';')
                        })
    
                        console.log(cluster_values)
                        data.data[key]['access_settings_table'].groups = cluster_values
                        
    
                    } );
                } );
    

    PreExisting entry, associated with ADMIN

    Proceed to Edit; notice ADMIN group is recognized by Select2

    Selected a couple options to add CUSTOMER, CUSTOMER SERVICE

    Successfully submitted and updated DB, notice the ';' - this comes from my preSubmit function.

    Click on the same field to edit again, no options are shown as they are not recognized due to the data being one string with semi colons.

    Is there a way I can separate the data using splice/slice before is it processed by datatables on the front end? Or maybe I am going about this the wrong way.

  • mikedmasonmikedmason Posts: 39Questions: 12Answers: 0

    I have found myself a solution to the preprocessing of the data as well.

    I have added the following to my ajax options;

    "ajax": {
                        "url": api.URL + "/dtapi/access", //API
                        "type": "POST",
                        "dataSrc": function(json){
                            var cluster_values = '';
                            var groups_array = [];
                            Object.keys(json.data).forEach(function(key){
                                var cluster_values = json.data[key]['access_settings_table'].groups
                                cluster_values = cluster_values.split(';')
                                
                                json.data[key]['access_settings_table'].groups = cluster_values;
                                
                                
    
                            })
                            
                            return json.data
                        }
                    }
    
    

    Hope this can help someone else in a similar situation.

This discussion has been closed.