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.