Okay
  Public Ticket #3601248
Frontend table filter "No matching records found"
Closed

Comments

  • Felix started the conversation

    Good day,

    I have several tables in my MySQL database, which are accessed via wpDataTables Input data source type: SQL query. Plus Server-side processing is enabled.

    All those tables do have lot's of columns. Mostly 30,000 - 100,000 rows.

    For all of them I do have the problem, that on the website the filter returns no value, also the data is there. It's saying: "No matching records found".

    This, although I don't use any complicated filters. It's pretty basic, to receive the complete table: SELECT * FROM `Domestic Substances List (DSL)`

    Please find attached screenshots.

    I also double checked some other tickets, prior opening this ticket. And it seems to be a general problem. See here:

    https://tmsplugins.ticksy.com/ticket/1963394/

    https://tmsplugins.ticksy.com/ticket/1856076/

    https://tmsplugins.ticksy.com/ticket/1286561/

    All those tickets doesn't offer a solution, as far as I understand. But maybe I'm mistaken.

    I also checked the documentation like eg. https://wpdatatables.com/documentation/creating-wpdatatables/creating-mysql-based-wpdatatables/ and  https://wpdatatables.com/documentation/table-features/server-side-processing/ 

    I like the documentation pretty much, but it doesn't helped me in this case.

    Best regards

    Felix

    Attached files:  Table frontend 1.png
      Table frontend 2 filtered.png
      Table backend 1 wpdatatables.png
      Table backend 2 phpMyAdmin.png

  •  1,729
    Miloš replied

    Hi Felix,

    Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

    -

    I can see what the issue is.

    Your table name has a space included - This will not work properly with our SQL Parser.

    The only way to correctly call the SQL Table name that has spaces is to include those accent grave symbols around it (`) , so like :

    SELECT * FROM `table name`.

    But if you include the accent graves, then the filtering won't work with our server-side processing feature.


    The solution ( if you need our server-side processing option enabled) is to rename your Table in the SQL Database.

    Just change the spaces to anything else, like underscore.

    Here is my example, my table was named "dummy employees" and that won't work properly;

    so i rename it to "dummy_employees" and now our Plugin is filtering it without any issue.


    4194749263.png
    5981505116.png
    9019375394.png
    8977033707.png

    Let me know how it goes, that should solve the issues.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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

  • Felix replied

    Hi Miloš,


    Thanks for your feedback.

    As per previous screenshots, the query had those english quotation marks: '

    So, this was not the problem. As suggested I now replaced the spaces with underscores, but the problem remains, as you can see per attached screenshots.

    Best regards

    Felix

    Attached files:  PhpMyAdmin_table.png
      wpDataTable_table.png
      website table.png
      website table search.png

  •  1,729
    Miloš replied

    Hi Felix,

    If this is the screenshot of the current Query, I can see you are still having the accent grave symbols (`) around the Table name.

    4440604238.png

    For the MySQL engine, we are dynamically adding the accent grave ( ` ), so there's no need to use it around the table name in the query. 

    If you were to delete them, searching and filtering should be fixed.

    So can you please try like this now :

    SELECT * FROM Domestic_Substances_List(DSL)

    and then the filtering should start working along with our server-side processing option.


    Let me know if that helps.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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

  • Felix replied

    Dear Miloš,

    Please note your feedback doesn't help and doesn't seem to be right.

    wpDataTables doesn't accept the accent grave symbols (`) around the Table name, as you can see as per attached screenshot.

    SELECT * FROM Domestic_Substances_List_(DSL)

    I'm forced to write it as follows:

    SELECT * FROM 'Domestic_Substances_List_(DSL)'
    

    Best regards

    Felix

    Attached files:  accent grave symbols.png

  •  1,729
    Miloš replied

    Hi Felix,

    Thank you for this feedback.

    You are right, our SQL Parser seems to struggle if we use any bracket symbol "()" in the Table Name.

    I also tried changing to other types of brackers, like squared [ or this one {  but it does not help/same result.


    I am passing this to our Developers - they will do their best to improve the SQL Parser in order for it to accept these symbols as part of the table name, even though we can't promise an ETA on it.

    7552374420.png

    For now, would it be possible for you to change your table name and not use any brackets?


    I also tried with | symbols, that will not work, sadly.

    For now, all I can come up with is using the underscores, so if i rename the SQL Table to Domestic_Substances_List_DSL

    in that case, you can have our server-side processing to work along with filtering/search and no sorting issues in your Tables.

    6815163912.png


    You can download my Video as a short demonstration here https://we.tl/t-kfO9okoCrG.

    I am sorry for this inconvenience.


    Let me know if that can work.

    Thank you.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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

  •  1,729
    Miloš replied

    Hi Felix,

    My apologies, I just recalled an alternate solution, forgot to mention it right away.

    2. If you need to keep these brackets in the SQL Table Name, you will be able to have filtering/search/sorting to work, if you can disable server-side processing in your Table.

    5440496982.png

    With our server-side processing disabled, then you can use the accent graves (`) around Table name without any filtering/search issues.


    But then, with server-side option disabled -  you will not be able to activate any Editing, so keep that in mind.


    Basically, it all comes down to server-side vs non-server-side.

     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 3188 you'll see this:

    if (is_array($res_dataRows) && count($res_dataRows) > 2000) {
    4647462986.png

    The lines of code can change during Updates because our Developers add new lines from time to time, so it is best to search for the line in any Code Editor with CTRL+F to get the right line.

    You can change the number 2000 to a value bigger than the number of rows in your table in order to still have that 'toggle option' for server-side processing.

    Another PHP file in this path :

    ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php

    In this file, please search for this, there should be two occurrences, first one around line 82 as :

    if (count($wpDataTable->getDataRows()) > 2000) {
    6921758130.png

    And second one around line 143:

    if (count($wpDataTable->getDataRows()) > 2000) {
    
    9764402593.png

    That will increase the server-side processing option automatic limit.

    We just advise caution not to increase that number 'too high' to avoid going over the limit what your Hosting Server's PHP Limitations can handle.

    Usually, up to around 5 to 6 thousand rows should pose no significant performance issues, but if you go higher than that data size without server-side processing, you could experience slow loading times.

    You can certainly test to see how a Table behaves on front-end, and if it loads too slow/or freezes the browser's Page, you can go back in the Table's Back-End and enable server-side processing for that Table.


    3. To summarize :

    If you need to have the brackets in table name - you can just disable server-side and have no filter/search issues;


    or if you need server-side enabled - then you would have to rename the SQL Table and remove any brackets, or any symbol that throws an error by our Parser.

    I realise that is not ideal - but at the moment, our SQL Parser has these limitations.


    Let me know if that helps.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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

  • Felix replied

    Dear Miloš,

    Please note I checked several times and now it does work, although I didn't do anything different.

    It seems sometimes it's a bit buggy, even when following the instructions one to one. I basically removed the complete database connection, the table which was done via SQL query and everything else and set up everything again.

    My setup, which works, is as follows (in case someone else is reading this):

    2056427365.png

    The same setup also works with a table via SQL query, containing 2,399,743 total entries. The search on the website just takes about 10 seconds, which is a bit long.


    Best regards

    Felix

  •  1,729
    Miloš replied

    Hi Felix,

    I just re-read the entire ticket, and I apologise if I am missing some major detail, but as far as I can see, there are a couple of important changes when we compare your latest screenshot, how you named this table;

    and what you used before.

    -

    Before, as we noticed, and as you pointed out, at some point you used a bracket as part of the table name.

    I can't honestly say exactly why that won't work, but our SQL Parser currently has the issue if we combine server-side processing when we set a bracket as part of the table name.

    Here is how your Query was before and a quote from earlier in the ticket :

    8808957551.png

    Then I made some tests and I can first confirm we are aware of the accent grave issue (`), for that  part :

    For the MySQL engine, we are dynamically adding the accent grave ( ` ), so there's no need to use it around the table name in the query. If you were to delete them, searching and filtering should work just fine.


    When it comes to using a bracket at the end of the table name like that, I did tests and can confirm this has an issue with our Parser when the server-side processing is enabled, so I advised avoiding the bracket at that time.

    So as explained in my last reply, at that point you had two choises :

    - Either to disable server-side processing and to have bracket as table name, that can work for filtering and our SQL Parser.

    - But since your Table has a lot more than 2 thousand rows, you can't disable server-side on that table, so then you needed to remove using a bracket from the table name.


    In the end, in this last message, I can see you removed any brackets from the Table name and now the QUery it is like :

    SELECT * FROM tbl_chem_INV_DSL
    3194923096.png

    So since you don't use any brackets now in the table name, there is no need for accent graves (~), thus our server-side processing should be working OK, as you reported.

    I am happy to see you managed to resolve it, that is the most important goal, of course.

    This ticket will surely be useful for other users who come across the same issue, thank you for sharing the update with us.



    2. In regards to having a slow load, we will do our best to help with as many details as possible, gathered from previous tickets experience :


    The loading speed depends on a lot of factors - your hosting plan, server's performance, amount of other data on the page, internet speed, etc. 

    For example, you can take a look at the table on our documentation, which contains around 2.600.000 rows, and see how it loads. The more data the table has, the more chance that it will impact the loading time.


    -

    You can also check, in this table's filter settings for columns, how many unique filtering values there are.

    If you have, for example - a string column that you set as a selectbox/multiselectbox, 

    and if it has more than 50 possible filtering values,

    you can go into that column setting, and check what is set for "Number of possible values to load" – 

    Here you can define how many possible values per page will be loaded in selectbox filter and editor inputs. It is recommended not to be set for All if you have more than 50 possible values for this column.

    2095790605.png
    1309831925.png

    So, check if that might be the case, try to lower the value to 50 and see if it helps the speed.

    -

    Also, it is possible for other plugins to affect the loading speed.

    We had a couple of cases with users who have an "optimization plugin" with specific settings, 

    such as  'SiteGround Optimizer', for example.

    These plugins have certain settings that can negatively affect the performance of our tables.

    If there is any JS minification going on,  there can be other settings, I can't say exactly,

    but something to be aware of.


    If you wish to do troubleshooting testing to check if an optimization/caching plugin might be slowing down tables, you can try to disable all plugins and just leave wpDataTables active - then clear all cache and reload the page, see if it loads faster.

    Then, if any optimization plugin Is used, try to revert all its settings to default, and if you use server-side tables ( such as SQL or Manual tables) ,  then exclude the pages where you have tables from caching.


    That is about all the advice i can give about this subject of slow load.

    If you have any additional questions about this topic, please open a new ticket and we will be happy to advise as much as our Support can cover.

    When you have multiple questions or issues which are for different subjects/topics,

     please open a new ticket for each subject, and we will help/advise more effectively. 

    In that way, issues and questions which are related to different subjects will be in separate tickets so other users or our support agents can find them easily. 

    Our policy is to have one issue or question per ticket for the reasons described already.

    Thank you for understanding.




    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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

  • Felix replied

    Dear Miloš,

    Thanks so much for your feedback, patience & all the hints you gave me.

    The ticket can be closed.

    Enjoy your weekend!

    Felix

  •  1,729
    Miloš replied

    Hi Felix,

    I am delighted to hear that this has been resolved. Thank you for letting us know. 

    Anything else that we could assist with, please don't hesitate to create new tickets.

    Have a great day!smile.png

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    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