Data Publisher and search

Hi Lars,

You can use the Data Publisher and add your own javascript code to add search conditions for specific columns as described in the example below:
https://datatables.net/examples/api/multi_filter.html

In this example however, all table data is loaded in the browser which allows jQuery DataTables to perform the search in the browser. By default, the Data Publisher performs a search server side which allows you to query large database tables without loading them completely in the browser. These are different strategies.

You can force the Data Publisher to send all output to the browser by entering the following JSON string in column Table options (advanced):
{"serverSide":false}
You can then use the code given in the example I mentioned above. Be aware that this is a perfect way to query small and medium sized tables. But I would not recommend to load all data into the browser for large tables.

Searching on a combination of columns is not supported by default, but you can add your own search logic to the plugin. To add your own search logic you can use filter wpda_construct_where_clause which. You can find more info about this filter here:
https://wpdataaccess.com/2020/01/29/filter-wpda-construct-where-clause-to-add-custom-search-behaviour/

If you follow the link below you’ll find a plugin written by Charles Godwin which uses this filter:
https://github.com/CharlesGodwin/wpda-cwg-extensions

Hope this helps!

Best regards,
Peter

Hi Peter

Great. And by the way, thank you for making this plugin. Okay, testing the Table options (advanced) in the Data Publisher, didn’t make it work for me. I was expecting the search boxes to appear beneath the table, but they didn’t show up. The Plugin from Charles Godwin did was I was asking for. Just wonderful.

Best regards

Lars

Good to hear it works Lars! ??

You can still add those search boxes beneath the table if your table is not too big to be loaded in your web page comnpletely. Just follow the example I mentioned above, set serverSide to false and add your own javascript.

Best regards,
Peter

Hi Peter

Can you provide me with some examples on how to use Data Publisher/Table options (advanced) ?. Maybe there is just some syntax’s I don’t get right, since I’m stuck on this one ?

Best regards

Lars

Hi Lars,

No problem! Here are a few examples which I use to test the plugin:

Load all data into the browser on startup:
{"serverSide":false}

Remember table state:
{"stateSave":true}

Define in which order areas are shown:
{"dom":"iptlfr"}

Turn off paging, load all data at one, set scroll bars:
{"paging": false,"serverSide":false,"info":false,"retrieve":true, "scrollY": "150px", "scrollCollapse": true,"scrollX":"0"}

Overwrite some plugin defaults:
{"pageLength":5,"stateSave":false,"bAutoWidth":true,"fixedColumns": true, "fixedHeader": true}

Add javascript function which sets color depending on content (more complex):
{ "paging":false, "serverSide":false, "fnRowCallback": "function(row, data, index) { if (data[1]=='Z 750 ABS') { jQuery(row).find('td:eq(1)').css('color','green'); } else { jQuery(row).find('td:eq(1)').css('color','red'); } }" }

>>> Your advanced settings MUST BE valid json! (no exceptions! use a validator!)
>>> You can use tabs and new lines to improve readability.
>>> Your advanced settings will overwrite plugin defaults if applicable.
>>> You should be able to use all available options, found here:
https://datatables.net/reference/option/
>>> For some options you need to install addtional extensions, found here:
https://datatables.net/extensions/index

I haven’t tested them all! ?? There are just too many combinations. The plugin installs extension Responsive by default. If you want to use other extensions, you need to install them manually.

If you have suggestions for improvement, I’m listening! ??

Best regards,
Peter