Hey there, Awesome Customers!

Just a heads up: Our team will be out of the office on Monday, November 11th in observance of Armistice Day, but don’t worry—we'll be back in full swing on Tuesday, November 12th.

In the meantime, you can explore our documentation for Amelia and wpDataTables. You'll find tons of helpful resources, including articles and handy video tutorials on YouTube (Amelia's YouTube Channel and wpDataTables' YouTube Channel), which might just have the answers you need while we’re away.

Thanks a bunch for your understanding and support!

Warm regards,

TMS

Okay
  Public Ticket #1078878
Search issue, separate MySQL connection and duplicate rows functionality
Closed

Comments

  •  4
    Enric started the conversation

    Hi wpDataTables team, 
    I'm using wpDataTables plugin in one of my projects and is working fine, but I have three small issues about which I would need help / support.

    1) For text filters, or the general search textbox, simple searches are working fine. The problem arise when I try to find by a more than one word that have other words in the middle. For example, if I make "THINKING ABOUT CHILDREN" or "THINKING" search, the results are being displayed correctly. Otherwise, if I try to make "THINKING CHILDREN" search, there are no results displayed. This is a must requirement for my client, and the only way that I've found for fix the issue is using % between terms ("THINKING % CHILDREN"). Is there any way to make this functionality possible making changes in the source code? I don't like to use % in a text box that is interacting with the database.

    2) Is it possible to have wpDataTables using the main WordPress MySQL connection and also using a separate MySQL connection or these options are exclusive?

    3) I have one MySQL datatable with more than 21000 rows that is created using MySQL based tables option. I only can use front-end edition to make changes on the data, and I would like to know if there is any way to duplicate existing rows to create new ones. This would be very helpful because a lot of rows are very similar between them and we only need to change 2-3 columns of the row to create new rows. In case not, is it contemplated to add this functionality in future releases?

    Thanks in advance for your help and support,

  • [deleted] replied

    Hi Enric,
    Thank you for the purchase.

    1. Unfortunately something like that is not possible because this search is performed by MySQL and when you type "THINKING CHILDREN", MySQL is not possible to find "THINKING ABOUT CHILDREN". in the database. The only way is to use % sign in the search box.

    2. Unfortunately no, at this time only one database can be used at the moment. We have plans to add more than one database in the future updates, but I can't tell you exactly when it will be because we re currently working on version 2.0.

    3. You can try Excel-like editor. You can copy/paste multiple rows, and this editor is more user friendly then standard wpDataTables editor. 

  •  4
    Enric replied

    Hi Milos, 

    1. Understood, but there should be any part on the source code where the text typed in the search box is sent to the database (I'm thinking in JS code). It would be possible to change this JS code before sending to the database formatting it properly? I perfectly understand that this is a customisation, I only need an orientation about the file(s) and (if possible) line of these files where this operation is being maked.

    2. Ok, thank you

    3. Unfortunately not, as I said I'm using MySQL based tables option (MySQL Query) and Excel-like edition is not available in this kind of tables. The unique way to make changes is by front-end edition, hence my question.

    Thanks for your help and kind regards,

  • [deleted] replied

    Hi Enric,

    1. The function responsible for getting the results from database is in file class.wpdatatable.php and it's called queryBasedConstruct. Maybe you can also try to use this filter:

    wpdatatables_filter_mysql_query( $query, $table_id )
    This filter is applied to the MySQL query before it is sent to MySQL server.
    $query is the query text, $table_id is the table identifier from the MySQL table (wp_wpdatatables).

    3. Maybe as a workaround you can add that table as Excel-like table in the front-end [wpdatatable id=1 table_view=excel] and enable Front-end editing. Like that you will be able to copy/paste rows but maybe the best option is to do that directly in the database.

  •  4
    Enric replied

    Hi Milos, and thanks for your quick response,

    1. Thank you!! Sounds good, I'll take a look to this documentation to try to achieve the desired functionality.


    3. Unfortunately, the workaround suggested is not a valid option. The table has more than 22000 rows and the performance is very, very slow when I try to use table_view=excel option. In addition, if I use this option the search and filter functionality dissapear, making almost impossible to work with the data in an agile mode. Finally, make the operations directly in the database is not possible because the final client don't have knowledge to interact directly with MySQL databases.
    So, turning back to my first message, is it contemplated in any way try to fix this problem in future releases, for example adding a duplicate row functionality in the standard editor?

    Finally, I have a new question, that I'll call number four for a better understanding.

    4. I have dates in one of the columns of my table, but I need to show only the year of this date. Is this possible in an easy way? I've take a look to the filters and I've found wpdatatables_filter_date_cell( $formattedValue ), perhaps this could be an option, but I need to make this format only in one of the fields.

    Thanks and kind regards,

  • [deleted] replied

    Hi Enric,

    3. Yes if you have that much rows it will be impossible to load wpDataTable using Excel option because all rows are loaded at the time.

    So, turning back to my first message, is it contemplated in any way try to fix this problem in future releases, for example adding a duplicate row functionality in the standard editor?

    - We are currently working on version 2.0 that will have a lot of new features and completely new design but unfortunately we do not have this task in our backlog. We will add it as an idea but I can promise you if we implement this functionality in some of the releases because it is not often requested by other users.

    4. it is not possible to show just years but maybe you can take a look at that filter that you have already found that is hooked on prepareCellOutput function in class.date.wpdatacolumn.php and see if it is possible to customize it. Without customization it would be possible if you set this column type to string but then sorting and filtering options will not work as they should for date column types.

  •  4
    Enric replied

    Hi Milos,

    Thank you for your help and support, I've finally got a provisional solution for problems 3 and 4.

    One last question: when are you planning to have finished the version 2.0? It will have performance improvements? 
    The problematic table that we are talking about these days has a bit slow performance in filter and edition operations.

    Best regards and have a nice weekend,

  • [deleted] replied

    Hi Enric,

    Did you enable Server-side processing for that table?

    We are also working on code refactoring and that should affect on performance, but also on performance is affecting a lot of other things like server performance, table size, etc.

    Version 2.0. should be released for 2 months but we can't promise anything.

  •  4
    Enric replied

    Hi Milos,

    Yes, Server-Side processing is enabled.

    Ok, thank you for the information, we can close the ticket.

    Thanks for your help and best regards,