Hi team,

 We are working for my client Bob Chaffin(Inspired Media Group) as developer, we have faced a issue in Wpdatatable plugin. Now we have created a table using mysql query in Wpdatatable plugin which have more than 5000 records. When we have put limit 2000 in my Mysql query all thinks working fine, but after removing limit from Mysql query  all records come but searching and advance filtering is not working.

So please advice what we do for the same.  

Thanks in Advance

Hi Milan,

                 Thanks for your response, but now we have faced a problem to create  a view because we have a subquery in from clause. You can review my query below. 

SELECT wp_invoices_table.purchase_date, wp_invoices_table.id_invoice, wp_invoices_table.id_client, wp_invoices_table.custom_identifier, wp_invoices_table.customer_name, wp_invoices_table.external_payment_method, wp_invoices_table.external_payment_number, wp_invoices_table.name, wp_invoices_table.purchase_total_price,wpuser.meta_value 
FROM wp_invoices_table
LEFT JOIN (SELECT wp_users.user_login, wp_usermeta.meta_value
FROM wp_users INNER JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID
AND wp_usermeta.meta_key = 'eth_address'
) AS wpuser ON CONCAT("gen", wp_invoices_table.custom_identifier ) = wpuser.user_login
WHERE wp_invoices_table.status =1

So creating view is not possible from our side, Please provide us another solution for the same or you can try to modify my query.    

Thanks for advance 

Hi Milan,

                We are waiting for your response, now we have change our query and now we have not use any join.  

SELECT invoicetb.purchase_date AS 'Purchase Date', 
       invoicetb.id_invoice AS 'Invoice ID', 
       invoicetb.id_client AS 'Crypto ID', 
       invoicetb.custom_identifier AS 'Distributor ID', 
       invoicetb.customer_name AS 'Name', 
       invoicetb.external_payment_method AS 'EPM', 
       invoicetb.external_payment_number AS 'EPN', 
       invoicetb.name AS 'Product', 
       invoicetb.purchase_total_price AS 'Price'
FROM wp_invoices_table AS invoicetb
WHERE invoicetb.status =1

We have still faced issue with searching and shorting. As we have observer there is not a query related issue and issue occurs when we fetch rows in big amounts, so please provide us a proper solution for the same asap. 

Thanks for advance