Okay
  Public Ticket #3718974
Custom post types / custom fields - with wpDataTables filter ?
Closed

Comments

  • Amy Turner started the conversation

    Hi - 

    Trying to get familiar with wpDataTables.

    I use JetEngine to create custom post types and fields.

    wpDataTable's query WP database table type works to pull in the custom field data, but when I enable advanced filtering, the filter doesn't work at all. Am I missing something?

    Thanks for your help.

  •  1,846
    Miloš replied

    Hi Amy,

    If your table has less than 2 thousand rows - try disabling the server-side processing option, and check does the filter work in that case?

    If so - there can be two ways to solve this, in short :

    1. Either to keep the server-side option disabled.

    2. Or, you can do some steps to try to 'simplify' the Query for our SQL Parser, so it can filter when combined with server-side processing.

    We will elaborate in more details why this happens :


    On this documentation, there are more details which explain how our server-side processing works :

    https://wpdatatables.com/documentation/table-features/server-side-processing/.

    Basically, when the server-side is enabled, the wpDataTables will give the search results through the Query;

    So, our Plugin sends the Query to the database.

    If that Query is formatted as

     "SELECT ...

     FROM ... 

    WHERE... "

    but after the "FROM" if it has any complex Query, there can be errors with sorting/filtering/table search;


    Our SQL Query feature does not work in the same way as a Database Management Tool ( such as PHPMyAdmin and similar),

    and is not meant to be used as one;

    Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types

    SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.

    Some of them are disabled for security reasons.

    Filtering, sorting, and search may not work properly if you include:

    • Accent graves ( ` ) around the table name
    • JOIN functions
    • UNION functions
    • CONCAT functions
    • sub-queries

    -

     Also, avoid calling a table name with a dot as part of the table name.

    For example, if you call table(  or VIEW) Name like "string1.string2 ", and if "string1" is your Database name - simply remove the DB name from the Query and just call the table by its actual name.


    If that is the actual table name in the DB -  you'll need to rename the table, because the PHP SQL parser we use sees the dot as a separator table.column, so if the table name is "string1.string2" it will see "string1" as the table, and "string2" as the column. 


    The data would return correctly, but filtering will not work.


    So, first you can check for accent graves around the table name, if you have this, remove it.

    Then, see if you used CONCAT to create any column.

    If so, go into this column setting, and disable it from "global search" in the Filtering tab.


    7138515403.png


    If none of that helps,

    you can try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.

     If you need help with that, you can see our video, where we show an example of using View in our plugin.


    To summarize, it can happen that a specific complex Query might work in PhpMyAdmin but struggles to work perfectly for sorting/filtering through our server-side processing ( and the SQL Parser);

    So when the Server-Side is enabled, our plugin sends a more complex SQL Query which in this case is too complicated for our Parser to handle,

    instead of when the server-side is disabled, it just simply filters the data just by the values already seen in the column.


    I hope that this helps to clarify everything.

    T solve this particular issue, there can be multiple ways :

    1. You can try to simplify your SQL Query in order for our server-side processing to work;

    2. Or try to make an SQL VIEW in your Database, then call it in our SQL Table like : SELECT * FROM ViewName


    3. Or if you can make it work without server-side processing, if your data/number of rows of  the table does not become too large, let's say above 4, 5, 6 thousand rows,  and if the hosting server performs well,

    you can 'get away' with disabling server-side on the table.

     If your SQL query based tables are not bigger than 2.000 rows, 

    you can disable server-side on SQL tables, and it will work like it does for Excel tables. ( loads all rows regardless of pagination)

    If you need to increase the row count while still having the "toggle" to disable server-side,

    Please go to ../wp-content/plugins/wpdatatables/source/class.wpdatatable.php and around line 2176 you'll see this:

    if (count($res_dataRows) > 2000) {

    You can change that number to a value bigger than the number of rows in your table.

    Same should be applied in ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php on lines 53:

    if (count($wpDataTable->getDataRows()) > 2000) {
    

    And line 100:

    if (count($wpDataTable->getDataRows()) > 2000) {
    

    That will increase the server-side automatic limit.


    I hope this helps.


    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Amy Turner replied

    Hi Miloš.

    Thank you so much for this detailed and well-explained response. It was quite helpful. Turning off the server-side processing made the filter work. I have not tried your other suggestions yet.

    I appreciate 1) the fast response, and 2) the thoroughness of your reply with detail and consideration for what I need to know to get this working. Thank you for including these details and links to documentation and videos.

    I've been looking for the right Wordpress data tables tool and tried many. However, because I want to use custom post types & fields as my data source and don't know SQL and PHP well enough to edit the query code, I guess this plugin may not be the right tool for me. I want a fully no-code option for creating dynamic data tables with custom post types/fields. If this is possible with wpDataTables in a different way or with a different table type and I'm just missing it, please let me know.

    Thank you again for your wonderful support.

    Amy

  •   Miloš replied privately