Hello LarryDaniele.

Thank you for being our long-term customer.

How many columns do you have in the table? There's no exact number, but server-side depends on rows * columns value. So, if you have a lot of columns and were do multiply that number with the number of rows, I believe it would explain why server-side is being automatically turned on. I'll check that with my developers, though.

Now, looking at the SQL file you sent, this is how the view is created, correct? I mean - the query within wpDataTables is something like this:

SELECT * FROM orders_view

Correct?

It shouldn't give you the problem with the filter if that is true. If, however the query in our plugin looks like this:

SELECT u.id as 'User ID',
um1.meta_value as 'Last Name', 
um2.meta_value as 'First Name', 
um_stone_harbor_street.meta_value AS 'Stone Harbor Street Address',
um_stone_harbor_unit.meta_value AS 'Unit #',
u.user_email AS Email,
um_billing_address_1.meta_value AS 'Billing Address 1',
um_billing_address_2.meta_value AS 'Billing Address 2',
um_billing_city.meta_value AS 'Billing City',
um_billing_state.meta_value AS 'Billing State',
um_billing_postcode.meta_value AS 'Billing Zip Code',
um_billing_country.meta_value AS 'Billing Country',
um_billing_phone.meta_value AS 'Phone',
p.ID as 'Order#', 
p.post_status as 'Order Status',
DATE_FORMAT(p.post_date, '%c/%e/%Y %H:%i') AS 'Order Create Date/Time',
DATE_FORMAT(p.post_modified, '%c/%e/%Y %H:%i') AS 'Order Change Date/Time',
oi.order_item_name AS 'Product Description',
pm_product_price.meta_value AS 'Cost',
oim_quantity.meta_value AS 'Quantity',
pm_order_total.meta_value AS 'Order Total',
pm_payment_method.meta_value AS 'Payment Method',
pm_transaction_id.meta_value AS 'Transaction#',
pm_transaction_fee.meta_value AS 'Transaction Fee',
DATE_FORMAT(FROM_UNIXTIME(um_expire_user_date.meta_value), '%Y') AS 'Expire Date'
FROM wp_posts as p
  LEFT JOIN wp_postmeta pm on pm.post_id = p.ID
  LEFT JOIN wp_users as u on u.ID = pm.meta_value
  LEFT JOIN wp_usermeta as um1 on um1.user_id = u.ID
  LEFT JOIN wp_usermeta as um2 on um2.user_id = u.ID
  LEFT JOIN wp_usermeta AS um_stone_harbor_street ON (u.ID=um_stone_harbor_street.user_id AND um_stone_harbor_street.meta_key='stone_harbor_street')
  LEFT JOIN wp_usermeta AS um_stone_harbor_unit ON (u.ID=um_stone_harbor_unit.user_id AND um_stone_harbor_unit.meta_key='stone_harbor_unit')
  LEFT JOIN wp_usermeta AS um_billing_address_1 ON (u.ID=um_billing_address_1.user_id AND um_billing_address_1.meta_key='billing_address_1')
  LEFT JOIN wp_usermeta AS um_billing_address_2 ON (u.ID=um_billing_address_2.user_id AND um_billing_address_2.meta_key='billing_address_2')
  LEFT JOIN wp_usermeta AS um_billing_city ON (u.ID=um_billing_city.user_id AND um_billing_city.meta_key='billing_city')
  LEFT JOIN wp_usermeta AS um_billing_state ON (u.ID=um_billing_state.user_id AND um_billing_state.meta_key='billing_state')
  LEFT JOIN wp_usermeta AS um_billing_postcode ON (u.ID=um_billing_postcode.user_id AND um_billing_postcode.meta_key='billing_postcode')
  LEFT JOIN wp_usermeta AS um_billing_country ON (u.ID=um_billing_country.user_id AND um_billing_country.meta_key='billing_country')
  LEFT JOIN wp_usermeta AS um_billing_phone ON (u.ID=um_billing_phone.user_id AND um_billing_phone.meta_key='billing_phone')
  LEFT JOIN wp_usermeta AS um_expire_user_date ON (u.ID=um_expire_user_date.user_id AND um_expire_user_date.meta_key='_expire_user_date')
  
  LEFT JOIN wp_postmeta AS pm_order_total on (p.ID=pm_order_total.post_id AND pm_order_total.meta_key='_order_total')
  LEFT JOIN wp_woocommerce_order_items AS oi on (p.ID=oi.order_id)
  LEFT JOIN wp_woocommerce_order_itemmeta AS oim_quantity on (oi.order_item_id=oim_quantity.order_item_id AND oim_quantity.meta_key='_qty')
  LEFT JOIN wp_woocommerce_order_itemmeta AS oim_product_id ON (oi.order_item_id=oim_product_id.order_item_id AND oim_product_id.meta_key='_product_id')
  LEFT JOIN wp_postmeta AS pm_product_price ON (oim_product_id.meta_value = pm_product_price.post_id AND pm_product_price.meta_key = '_price' )
  LEFT JOIN wp_postmeta AS pm_payment_method ON (p.ID = pm_payment_method.post_id AND pm_payment_method.meta_key = '_payment_method' )
  LEFT JOIN wp_postmeta AS pm_transaction_id ON (p.ID = pm_transaction_id.post_id AND pm_transaction_id.meta_key = '_transaction_id' )
  LEFT JOIN wp_postmeta AS pm_transaction_fee ON (p.ID = pm_transaction_fee.post_id AND pm_transaction_fee.meta_key = 'PayPal Transaction Fee' )
  
WHERE   p.post_type = 'shop_order' 
AND     pm.meta_key = '_customer_user'
AND     um1.meta_key = 'last_name'
AND     um2.meta_key = 'first_name'

Then, I believe the plugin is not able to successfully read the query. It probably has problems parsing the query.

Basically, if you were to create a view from the query above, and pull it in wpDataTables in a single line SELECT * FROM view - it should be working fine.

Best regards.

Hey LarryDaniele.

The server-side is being turned on automatically after 2000 rows after all. So, with this in mind - there is a way to change this to 3000 or 4000, but it will cause the table to load slower.

If you'd still like this to be implemented, please let me know, and I'll try to make it work for you during the weekend. Also, it is worth mentioning that once the plugin updates the process will have to be repeated because an update will overwrite the workaround.

I'll wait for your reply.

Best regards.

You are welcome LarryDaniele. Glad I could help.

If you have any more issues or questions feel free to open a new ticket, we will gladly help.

We'd greatly appreciate it if you could take a minute and leave a Review on CodeCanyon on this link. Thanks! 

Best regards.

Thank you very much Larry!

I will forward this to our development team immediately, so they can take it into consideration.

Best regards.

Hi Jorge.

Sure, that info doesn't contain any personal data, so I can share it with you again.

You would need to change the following files:

../wp-content/plugins/wpdatatables/wpdatatables.php - Add this line of code:

if (!defined('WDT_FORCED_SERVER_SIDE_PROCESSING_ROW_COUNT_THRESHOLD')) {
    define('WDT_FORCED_SERVER_SIDE_PROCESSING_ROW_COUNT_THRESHOLD', 2000); // Number of rows that triggers manditory server-side processing
}

Then, in ../wp-content/plugins/wpdatatables/source/class.wpdatatable.php change line 2092 from:

if (count($res_dataRows) > 2000) {

to:

if (count($res_dataRows) > WDT_FORCED_SERVER_SIDE_PROCESSING_ROW_COUNT_THRESHOLD) {

In ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php change line 53 from:

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

to:

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

And in ../wp-content/plugins/wpdatatables/templates/admin/table-settings/table_settings_block.inc.php change line 167 from:

title="<?php _e('If it is turned on, all sorting, filtering, pagination and other data interaction will be done by MySQL server. This feature is recommended if you have more than 2000-3000 rows. Mandatory for editable tables.', 'wpdatatables'); ?>">

to:

title="<?php _e('If it is turned on, all sorting, filtering, pagination and other data interaction will be done by MySQL server. This feature is recommended if you have more than '.WDT_FORCED_SERVER_SIDE_PROCESSING_ROW_COUNT_THRESHOLD.' rows. Mandatory for editable tables.', 'wpdatatables'); ?>">

So, then a user can add a block like this to wp-config.php to increase the limit (at their own risk):

// Override wpDataTables default threshold of "2000" rows for forced server-side processing
define('WDT_FORCED_SERVER_SIDE_PROCESSING_ROW_COUNT_THRESHOLD', 100000);

LarryDaniele shared this with us, and made these exact changes and it is working well for them.

I hope this helps.

Best regards.