SearchPanes, ServerSide with NodeJS, Bad SQL errors

MarkAndersonUK

Hi,

First off, I am quite a novice, so please forgive any obvious errors or lack of understanding.

I am struggling with SearchPanes when using ServerSided processing, it works fine non-serverside, but
when I try to configure the simplest example I am getting "Error: ER_PARSE_ERROR: You have an error in your SQL syntax; "

I have done some investigation, and have found the SQL (mySQL) being generated is indeed invalid and appears to be picking up some unwanted LeftJoin info. The generated SQL looks like this :

SELECT distinct clients.HomeState as label, clients.HomeState as value, count(*) as total from clients left join compass.agent as agent2 group by clients.HomeState;

The LeftJoin info is incomplete and I don't know why it is being included, the left join info by other fields.

Here is my NodeJS controller code:

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")
                .setFormatter( Format.ifEmpty( null ))
                .options(new Options()
                    .table('compass.agent as agent2')
                    .value('AgentReference')
                    .label(['FirstName', 'LastName'])
                    .where(function() {
                        this.whereNotNull('npn'),
                            this.where('status', '=', 'Active')
                    })
                ),
            new Field("agent2.FirstName"),
            new Field("agent2.LastName"),
            new Field("clients.FirstName"),
            new Field("clients.LastName"),
            new Field("clients.EMail1"),
            new Field("clients.HomeState")
                .searchPaneOptions(new SearchPaneOptions()),
            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 in the nodeJS View is :

    script(type='text/javascript').
        (function ($) {
            $(document).ready(function () {
                var editor = new $.fn.dataTable.Editor({
                    ajax: '/Contacts/api/clients',
                    table: '#clients',
                    fields: [
                        {label: "CPN ID:", name: "clients.Id"},
                        {label: "InfusionSoft ID:", name: "clients.infusionsoft_id"},
                        {label: "Agent:",name: "clients.AgentId",type: "select",placeholder: "Select an Agent Owner"},
                        {label: "First Name:", name: "clients.FirstName"},
                        {label: "Last Name:", name: "clients.LastName"},
                        {label: "EMail1:", name: "clients.EMail1"},
                        {label: "State (Home):", name: "clients.HomeState"},
                        {label: "ZipCode (Home):", name: "clients.HomeZipCode"}
                    ]
                });

                var clients_table = $('#clients').DataTable({
                    dom: 'PBlfrtip',
                    ajax: {
                        url: '/Contacts/api/clients',
                        type: "POST"
                    },
                    language: {
                        searchPanes: {
                            collapse: {0: 'Search Options', _: 'Search Options (%d)'}
                        }
                    },
                    serverSide: true,
                    processing: false,
                    columns: [
                        {data: "clients.Id"},
                        {data: "clients.infusionsoft_id"},
                        {data: null, editField: "clients.AgentId", "searchable": false,
                            render: function (data) {
                                return data.agent2.FirstName + ' ' + data.agent2.LastName;
                            }
                        },
                        {data: "clients.FirstName"},
                        {data: "clients.LastName"},
                        {data: "clients.EMail1"},
                        {data: "clients.HomeState"},
                        {data: "clients.HomeZipCode"}
                    ],
                    columnDefs: [
                        {searchPanes: {show: true}, targets: [7]}
                    ],
                    paging: true,
                    fixedColumns: true,
                    lengthChange: true,
                    lengthMenu: [ [15, 30, 50, -1], [15, 30, 50, "All"] ],
                    searching: true,
                    buttons: [ {extend: 'searchPanes'},
                    { extend: "create", editor: editor },
                    { extend: "edit",   editor: editor },
                    { extend: "remove", editor: editor }
                    ],
                    select: true
                });
            });
        }(jQuery));

I appears to me that the LeftJoin that is used by the AgentID field to display the Name of a person in a SELECT box,
is being partially applied to the SQL sent to the server to obtain the list of States found in the main resultset from the client table. I have no idea if this is a bug, or (more likely) something stupid I have done.

I would appreciate any help you might have,

I am using mySQL, NodeJS, Datatables and a licensed copy of Editor.

Thank you.

sandy

Hi @MarkAndersonUK ,

On the server side, SearchPanes applies any left join that is also to be applied to the overall Editor instance.

Due to an oversight on our part, SearchPanes didn't know how to cope with a function as a second argument like you have above. I've raised an issue internally (DD-2424 for my reference) and have made a fix. If you pull a fresh copy from the GitHub repo then it should hopefully work for you.

Thanks,
Sandy

MarkAndersonUK

Thank you so much @sandy,

Please forgive me, as I mentioned I am very much a novice at this, and also mainly a windows user ( another point against I am sure, from some people)..

I am not very sure exactly what I am supposed to do, I took a look at the GitHub Repo, and I can see the fixes you put in, but the files don't match the filenames that I am using (when I downloaded Editor-NodeJS-2.0.5.zip , I am sure I could wait for the official release the includes all these fixes, but I would love to apply them now.. I am happy to read any other post or instruction set, if you could point me in the right direction.

Again, my apologies for this, I am sure many people probably know what to do, but I am totally new to NodeJS and Javascript, having come from a mainly DB background.

Thanks for any tips you might have.

sandy

Hi @MarkAndersonUK ,

Probably easiest for you to just wait for the next Editor release. We are aiming to release it early next week - so not long!

Thanks,
Sandy

MarkAndersonUK

Thanks @sandy,

I can wait.. I am eager to learn too, so if you have any links that might explain how to implement the changes, I would love to read them.

Also, I think there maybe another bug in the SQL creation routines for ServerSide searching. If you take a look at the forum question below, you can see that when I do a search bad SQL is being produced if I have a field that uses SQL Statements. Here is the forum link (you need to read my comment to see the error :

https://datatables.net/forums/discussion/71175/error-when-trying-to-use-column-based-on-field-using-sql-functions-serverside-nodejs#latest