Error when trying to use column based on field using 'SQL functions' / ServerSide / NodeJS

Error when trying to use column based on field using 'SQL functions' / ServerSide / NodeJS

MarkAndersonUKMarkAndersonUK Posts: 43Questions: 16Answers: 0

Hi,

When I attempt to use an 'SQL function' to define a field, it appears to work fine, the value is returned
in my AJAX data. However, when I reference that field as the data source for a column, I get the following error:

Uncaught TypeError: Cannot read properties of undefined (reading 'style')
    at Ya (jquery.dataTables.min.js:76)
    at Ba (jquery.dataTables.min.js:62)
    at f (jquery.dataTables.min.js:107)
    at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:108)
    at Function.each (jquery.min.js:2)
    at k.fn.init.each (jquery.min.js:2)
    at k.fn.init.u [as dataTable] (jquery.dataTables.min.js:98)
    at k.fn.init.l.fn.DataTable (jquery.dataTables.min.js:187)
    at HTMLDocument.<anonymous> (Contacts:79)
    at e (jquery.min.js:2)

Here is a copy of my NodeJS controller :

router.all('/api/clients', async function(req, res) {
    let editor = new Editor(ot198, 'clients', 'clients.Id')
        .fields(
            new Field("clients.Id"),
            new Field("clients.infusionsoft_id"),
            new Field("clients.AgentId"),
            new Field("agent2.FirstName"),
            new Field("agent2.LastName"),
            new Field("clients.FirstName"),
            new Field("clients.MiddleName"),
            new Field("clients.LastName"),
            new Field("concat_ws(' ',clients.FirstName,clients.MiddleName,clients.LastName)", "clients.FullName" )
                .set( false ),
            new Field("clients.EMail1"),
            new Field("clients.HomeState"),
            new Field("clients.HomeZipCode"),

        )
        .leftJoin( 'compass.agent as agent2', function () {
            this.on('agent2.AgentReference', '=', 'clients.AgentId')
        })

    await editor.process(req.body);
    res.json(editor.data());
});

The Javascript for the page:

var clients_table = $('#clients').DataTable({
    dom: 'Blfrtip',
    ajax: {
        url: '/Contacts/api/clients',
        type: "POST"
    },
    serverSide: true,
    processing: false,
    columns: [
        {data: "clients.Id"},
        {data: "clients.infusionsoft_id"},
        {data: "clients.AgentId"},
        {data: null,
            editField: ['clients.FirstName', 'clients.MiddleName', 'clients.LastName'],
            className: 'bbledit',
            "searchable": false,
        },
        {data: "clients.FullName"},
        {data: "clients.EMail1",
            render: function (toFormat) {
                var tEmail;
                tEmail = toFormat.toString();
                tEmail = '<a href="mailto:' + tEmail + '" target="_blank">' + tEmail + '</a>';
                return tEmail
            }},
        {data: "clients.HomeState"},
        {data: "clients.HomeZipCode"},

    ],
    paging: true,
    fixedColumns: true,
    lengthChange: true,
    lengthMenu: [ [15, 30, 50, -1], [15, 30, 50, "All"] ],
    searching: true,
    colReorder: true,
    responsive: true,
    buttons: [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ],
    select: true,
});

If I remove the columns entirely, the page loads and I am able to capture the AJAX data, and the column does exist, and does have the correct data in it, here is an extract of one of the records:

      "data": [
        {
          "DT_RowId": "row_1",
          "clients": {
            "Id": 1,
            "infusionsoft_id": 3832,
            "AgentId": 161,
            "FirstName": "Brenda",
            "MiddleName": "",
            "LastName": "Smith",
            "LastName2": "Smith",
            "FullName": "Brenda  Smith",
            "EMail1": "Smith@mail.com",
            "HomeState": "UT",
            "HomeZipCode": "84095"
          },
          "agent2": {
            "FirstName": "Shem",
            "LastName": "Barlow"
          }
        },

If anyone is interested, the reason I am trying to load the name fields a second time, is to allow the search function to to work on the name fields, as I am using a multi-field bubble edit on the name, which causes the serverside search to fail, so the intention is to load the name fields a second time (in a single CONCAT_WS field) and make that field hidden, thus allowing the search on those fields to still work. If there is a simpler way, I would love to hear about it.

At all appears to be working just as I imagined, all but the error message I get when I try and add the field to a column.

Any help would be most appreciated.

Thanks !!

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    Cannot read properties of undefined (reading 'style')

    This error typically means there is a mismatch in the number of columns defined in HTML and in Datatables. Looks like you have 8 columns defined in Datatables. Do you have 8 defined in HTML?

    Kevin

  • MarkAndersonUKMarkAndersonUK Posts: 43Questions: 16Answers: 0

    Thanks @kthorngren,

    You are correct, adding the HTML appears to fix the initial problem, however, it created another problem, when I try and perform a search, I am getting an SQL Error now, and it is really strange, the SQL looks invalid. Take a look :

    The search term I typed is : anderson

    Here is the SQL that is sent to the server :

    SELECT count(`clients`.`Id`) as `cnt` from `clients` left join `compass`.`agent` as `agent2` on `agent2`.`AgentReference` = `clients`.`AgentId` 
    WHERE (`clients`.`Id` like '%anderson%' 
    or `clients`.`infusionsoft_id` like '%anderson%' 
    or `concat_ws(' ',clients`.`FirstName,clients`.`MiddleName,clients`.`LastName)` like '%anderson%' 
    or `clients`.`EMail1` like '%anderson%' 
    or `clients`.`HomeState` like '%anderson%' 
    or `clients`.`HomeZipCode` like '%anderson%' 
    or `clients`.`Phone1Number` like '%anderson%' 
    or `clients`.`Phone2Number` like '%anderson%');
    

    The SQL is invalid, around the SQL Statement section, the ` characters are
    placed wrongly, the corrected SQL should be :

    Replace :

    or `concat_ws(' ',clients`.`FirstName,clients`.`MiddleName,clients`.`LastName)` like '%anderson%' `
    

    With :

    or concat_ws(' ',`clients`.`FirstName,clients`.`MiddleName`,`clients`.`LastName`) like '%anderson%'
    

    However, I cannot find a way to correct this myself.

    Finally, the error is :

    (node:4464) UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`LastName)` like '%anderson%' or `clients`.`EMail1` like '%anderson%' or `clien' at line 1
        at Query.Sequence._packetToError (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
        at Query.ErrorPacket (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
        at Protocol._parsePacket (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\Protocol.js:291:23)
        at Parser._parsePacket (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\Parser.js:433:10)
        at Parser.write (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\Parser.js:43:10)
        at Protocol.write (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\Protocol.js:38:16)
        at Socket.<anonymous> (C:\SiteRoots\compass_admin\node_modules\mysql\lib\Connection.js:88:28)
        at Socket.<anonymous> (C:\SiteRoots\compass_admin\node_modules\mysql\lib\Connection.js:526:10)
        at Socket.emit (events.js:210:5)
        at addChunk (_stream_readable.js:308:12)
        at readableAddChunk (_stream_readable.js:289:11)
        at Socket.Readable.push (_stream_readable.js:223:10)
        at TCP.onStreamRead (internal/stream_base_commons.js:182:23)
        --------------------
        at Protocol._enqueue (C:\SiteRoots\compass_admin\node_modules\mysql\lib\protocol\Protocol.js:144:48)
        at Connection.query (C:\SiteRoots\compass_admin\node_modules\mysql\lib\Connection.js:198:25)
        at C:\SiteRoots\compass_admin\node_modules\knex\lib\dialects\mysql\index.js:132:18
        at new Promise (<anonymous>)
        at Client_MySQL._query (C:\SiteRoots\compass_admin\node_modules\knex\lib\dialects\mysql\index.js:126:12)
        at executeQuery (C:\SiteRoots\compass_admin\node_modules\knex\lib\execution\internal\query-executioner.js:37:17)
        at Client_MySQL.query (C:\SiteRoots\compass_admin\node_modules\knex\lib\client.js:144:12)
        at Runner.query (C:\SiteRoots\compass_admin\node_modules\knex\lib\execution\runner.js:130:36)
        at ensureConnectionCallback (C:\SiteRoots\compass_admin\node_modules\knex\lib\execution\internal\ensure-connection-callback.js:13:17)
        at Runner.ensureConnection (C:\SiteRoots\compass_admin\node_modules\knex\lib\execution\runner.js:272:20)
        at processTicksAndRejections (internal/process/task_queues.js:93:5)
        at async Runner.run (C:\SiteRoots\compass_admin\node_modules\knex\lib\execution\runner.js:30:19)
    (node:4464) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 8)
    
  • sandysandy Posts: 913Questions: 0Answers: 236

    Hi @MarkAndersonUK ,

    What version of the editor libraries are you using to produce this? It would be worth updating to the latest version and seeing if it occurs there.

    On another note, it is actually possible to avoid this by combining the fields on the client side rather than the server side by using a renderer. You can see an example of how to do this here.

    Thanks,
    Sandy

  • MarkAndersonUKMarkAndersonUK Posts: 43Questions: 16Answers: 0

    Hi @sandy,

    The libraries I am using are :

    jquery.dataTables.min.js - 1.11.3
    dataTables.editor.min.js - 2.0.5

    I tried loading using npm, but using NodeJS, I just get errors
    when I tried to follow the NPM instructions, I recall it was
    'ReferenceError : window is not defined at object. ' when trying to use it. (I read that this is because window does not exist in NodeJS), I searched the forum but could not find anyone with a working example using NPM and NodeJS.

    I will try the renderer method and see it if fixed the issues, as I am just having an awful time getting this to work nice.. The latest thing is I can't get the basic search to to work in ServerSide mode.. For example, I have a name split into multiple fields (first, middle and last), in Local mode, the search is able to find records if I type the name 'Bob Smith' (for example), but in serverside, it does not find the records.. The search function appears to work quite differently and so far I cannot figure a way around it.. I don't want to confuse this discussion, so I will create a new discussion about it... Thanks for you help.

  • MarkAndersonUKMarkAndersonUK Posts: 43Questions: 16Answers: 0
    edited January 2022

    Hi @sandy, I tried using the locally rendered method to join the fields, but
    this does not solve the problem as the problem is when we search, the SQL produced was invalid. While that no longer happens, we are not able to include the locally rendered fields at all in a search, if we try the following error occurs :

    UnhandledPromiseRejectionWarning: Error: Unknown field: (index 3)

    I need to create the concatenated fields on the server-side, so they are included in the basic search function, as I displayed earlier when I do that, the SQL being produced by the search function is invalid, and the ** ` ** characters are placed wrongly or are missing in required places. That error is :

    UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.LastName) like '%anderson%' or clients.EMail1 like '%anderson%' or `clien' at line 1

    (full error details are in my earlier post)

    How can I fix this invalid SQL? is there a way that I can override the SQL generated? If not, do you have any suggestions? If this is a bug, do I have to post a report somewhere, other than this forum post, to get it addressed?

    I appreciate all your help and advice.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    Hi,

    Sandy asked if I could jump in here and take a look at this. Apologies you are running into this issue. I've been looking at our code base, and I'm afraid that at the moment we do a really horrible job of handling the concat function (or almost any function) due to how our aliasing of column names work. That is an error and something we need to address.

    However, I do have a workaround I think - drop the concat on the server-side and do that rendering entirely on the client-side. The search should then work correctly. Use something like:

    {
      data: 'clients.FirstName',
      render: function (data, type, row) {
        return data + ' ' + row.clients.MiddleName + ' ' + row.clients.LastName;
      }
      editField: ['clients.FirstName', 'clients.MiddleName', 'clients.LastName'],
      className: 'bbledit'
    }
    

    Note that if you do a column search, it will be based on the FirstName - likewise the sorting. Global search will work across them all.

    If that is a problem, I think a VIEW would be the only workaround at the moment. That would encapsulate the concat at the server-side, so Editor just sees a regular table to read from.

    Sorry I don't have a better solution at the moment. It is going to involve us changing how we index the database parameters internally.

    Allan

  • MarkAndersonUKMarkAndersonUK Posts: 43Questions: 16Answers: 0

    Hi @allan,

    I think I found a solution that works for me, like you suggested, I used a view to concat the fields on the server. I created some hidden cols from this view, that contain the full name (without middlename) and full name (with middlename). This allows the basic search box to find the records based on any name combination.

    At first I tried to use the 'readTable' option to select a view, but when using serverside, that gave me a lot of errors, where it could not save anything, despite using the 'editField' option to set the field and including the table names. I just could not get it to work at all. So I finalized, on using one table for the main read/write data, and a self-referencing joined table for all of the search fields I wanted.

    I wanted to thank both you (@allan) and @sandy for all your help. I love the editor, and am now another step closer to finishing my project. THANKS

    This worked for me, and perhaps might work for others using a similar setup, so here is what I basically ended up with (I removed fields and options to make this example simpler) :

    **NodeJS Controller Extract: **

    router.all('/api/clients', async function(req, res) {
        let editor = new Editor(ot198, 'clients', 'clients.Id')
            .fields(
                new Field("clients.Id"),
                new Field("clients.infusionsoft_id"),
                new Field("clients.AgentId"),
                new Field("clients.FirstName"),
                new Field("clients.MiddleName"),
                new Field("clients.LastName"),
                new Field("cl2.FullName1")
                    .set( false ),
                new Field("cl2.FullName2")
                    .set( false )
            )
    
            .leftJoin( 'cap_recruits_fullname_list1 as cl2', function () {
                this.on('cl2.Id', '=', 'clients.Id')
            })
    
        await editor.process(req.body);
        res.json(editor.data());
    });
    

    **The Javascript section: **

        script(type='text/javascript').
            (function ($) {
    
                $(document).ready(function () {
                    var editor = new $.fn.dataTable.Editor({
                        ajax: '/Recruits/api/clients',
                        table: '#clients',
                        fields: [
                            {label: "CPN ID:", name: "Id"},
                            {label: "InfusionSoft ID:", name: "infusionsoft_id"},
                            {label: "Full Name:", name: "cl2.FullName1"},
                            {label: "Full Name2:", name: "cl2.FullName2"},
                            {label: "First Name:", name: "clients.FirstName"},
                            {label: "Middle Name:", name: "clients.MiddleName"},
                            {label: "Last Name:", name: "clients.LastName"}
                        ]
                    });
    
                    var clients_table = $('#clients').DataTable({
                        dom: 'Blfrtip',
                        ajax: {
                            url: '/Recruits/api/clients',
                            type: "POST"
                        },
                        serverSide: true,
                        processing: false,
                        columns: [
                            {data: "clients.Id"},
                            {data: "clients.infusionsoft_id"},
                            {data: "cl2.FullName1", visible: false},
                            {data: "cl2.FullName2", visible: false},
                            {data: "clients.FirstName"},
                            {data: "clients.MiddleName"},
                            {data: "clients.LastName"}
                        ],
    
                        paging: true,
                        fixedColumns: true,
                        lengthChange: true,
                        lengthMenu: [ [15, 30, 50, -1], [15, 30, 50, "All"] ],
                        searching: true,
                        responsive: true,
                        buttons: [
                            { extend: "searchBuilder", editor: editor },
                            { extend: "create", editor: editor },
                            { extend: "edit",   editor: editor },
                            { extend: "remove", editor: editor }
                        ],
                        select: true,
                    });
                });
            }(jQuery));
    

    **The VIEW on the SQL : **

    VIEW `cap_recruits_fullname_list1` AS
        SELECT 
            `c`.`Id` AS `Id`,
            CONCAT_WS(' ', `c`.`FirstName`, `c`.`LastName`) AS `FullName1`,
            IF((LENGTH(`c`.`MiddleName`) > 0),
                CONCAT_WS(' ',
                        `c`.`FirstName`,
                        `c`.`MiddleName`,
                        `c`.`LastName`),
                CONCAT_WS(' ', `c`.`FirstName`, `c`.`LastName`)) AS `FullName2`
        FROM
            `clients` `c`
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    That is excellent - delighted to hear you've got this up and running as you need now. Thank you for your kind words, and also highlighting an area where we need to improve things. I had knowing the indexing of our db parameters was a bit of an issue, before, but I can see it is actually more of a problem than I'd thought of before. Going to be looking at addressing that for 2.1.

    Regards,
    Allan

Sign In or Register to comment.