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 https://github.com/jpravetz/node-datatable/issues/14#issuecomment-198647259.

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: https://github.com/jpravetz/node-datatable/blob/ca25ef76b94743ee8101320baae37615797676db/lib/builder.js#L199 https://github.com/jpravetz/node-datatable/blob/ca25ef76b94743ee8101320baae37615797676db/lib/builder.js#L152

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