Same output for all Query Builder

[nikunj-digicorp]

I'm getting same output for all the method calls from generate.js, am I missing anything?

Called 1
Queries:
{"recordsTotal":"SELECT COUNT(id) FROM Orgs","select":"SELECT * FROM Orgs"}
Called 2
Queries:
{"recordsTotal":"SELECT COUNT(id) FROM Orgs","select":"SELECT * FROM Orgs"}
Called 3
Queries:
{"recordsTotal":"SELECT COUNT(id) FROM Orgs","select":"SELECT * FROM Orgs"}
Called 4
Queries:
{"recordsTotal":"SELECT COUNT(id) FROM Orgs","select":"SELECT * FROM Orgs"}

[nikunj-digicorp]

Is there any working demo available on git that support MySQL and JQuery DataTable?
Can you please help me to short out this?
Thanks in Advance! 👍
BTW, The code is nicely managed.

[jpravetz]

It's been awhile since I worked with SQL. I'd have to spend more time than I have to look at your first message. Sorry. Also, the only example I had was proprietary, so I can't share it (and it was quite complex).

[nikunj-digicorp]

Thank you for answer.

[thbl]

got the same error, did you find out how to use it ?

[ghybs]

Hi,

FWIW, in order to have ORDER and WHERE / SEARCH being correctly taken into account, make sure to define your columns specifying their name as well, as mentioned in #14 (comment).

In my version of DataTables (not sure if behaviour changed at some point), the data / requestQuery parameter passed by DataTables to the ajax function option also had columns' orderable and searchable options defined as boolean's, whereas node-datatable compares them strictly with string values:

node-datatable/lib/builder.js

Line 199 in ca25ef7

if (column.orderable === 'true' && column.name) {

node-datatable/lib/builder.js

Line 152 in ca25ef7

if (customColumns || column.searchable === 'true'){

If you are in that case, you have to convert these fields first into strings before passing the refactored requestQuery to buildQuery() method.

I had no problem at all regarding the PARTIAL / LIMIT query.

Hope this helps.

[ghybs]

Live demo of this bug: https://plnkr.co/edit/auaYxFKq1bOVU4IhcP2T?p=preview

An example of workaround:

function formatRequestQuery(requestQuery) {
  requestQuery.columns.forEach(function(column) {
    formatColumnField(column, 'orderable');
    formatColumnField(column, 'searchable');
  });

  return requestQuery;
}

function formatColumnField(column, fieldName) {
  if (typeof column[fieldName] === 'boolean') {
    column[fieldName] = column[fieldName] ? 'true' : 'false'
  }
}

Use it to refactor the requestQuery before passing it to queryBuilder.buildQuery() method.

See it in action: https://plnkr.co/edit/PZiw9h5z0AgqSAQIfcXM?p=preview