Wordpress WP_List_Table sort data query problem

Question

I'm using wordpress 5.5 and php 7.4 and mysql 5.7.30 I'm using woocomerce subscription plugin for my e-commerce system. what I want is to be able to show list of paginatd subscriptions in admin panel and (important part) sort them based on "next_payment_schedule", "first_payment_schedule" date in admin panel. woocommerce subscription plugin is persisting all subscriptions as custom post types which thier type is : "shop_subscription" and also is persisting all their specific fields in postmeta table.

the problem is as I said this plugin is persisting these dates in postmeta table. how can I support both pagination and sorting ?

here is my current query :

SELECT p.id AS post_id, p.post_status, u.id AS user_id , u.user_login, u.user_email, GROUP_CONCAT(pm.meta_value)
FROM posts AS p
INNER JOIN users AS u ON p.post_author = u.id
INNER JOIN postmeta AS pm ON pm.post_id = p.id 
WHERE post_type = 'shop_subscription'
AND pm.meta_key in ('_schedule_start', '_schedule_next_payment')
GROUP BY p.id
ORDER BY user_login ASC

this query is fine when I want to handle pagination.

and the following query is fine when I want to handle sorting,

SELECT p.id AS post_id, p.post_title, p.post_status, p.post_name, u.id AS user_id , u.user_login, u.user_email, pm.meta_key, pm.meta_value from posts AS p
INNER JOIN postmeta AS pm ON pm.post_id = p.id 
INNER JOIN users AS u ON p.post_author = u.id 
WHERE post_type = 'shop_subscription' 
AND pm.meta_key in ('_schedule_start', '_schedule_next_payment') 
ORDER BY CASE WHEN pm.meta_key IN('_schedule_next_payment') THEN pm.meta_value END desc

How should I handle this situation ?

score 0 · Accepted Answer · answered Sep 01 '20 at 05:30

At the end I solved my problem using this query :

        $sql = "SELECT p.id AS post_id, p.post_status, u.id AS user_id , u.user_login, u.user_email, pm2.schedule_start,  pm2.schedule_next_payment
                FROM `$wpdb->posts` AS p
                INNER JOIN `$wpdb->users` AS u ON p.post_author = u.id
                INNER JOIN (
                    SELECT t.post_id, 
                           MAX(t.schedule_start) AS schedule_start, 
                           MAX(t.schedule_next_payment) AS schedule_next_payment
                    FROM (
                
                    SELECT 
                            post_id,
                            case when pm.meta_key = '_schedule_start' then pm.meta_value else 0 end AS schedule_start,
                            case when pm.meta_key = '_schedule_next_payment' then pm.meta_value else 0 end AS schedule_next_payment
                            FROM (SELECT * FROM `$wpdb->postmeta` pm where pm.meta_key IN ('_schedule_next_payment', '_schedule_start')) AS pm
                    ) AS t
                    GROUP BY t.post_id
                ) 
                
                AS pm2 ON pm2.post_id = p.id
                WHERE post_type = '".self::POST_TYPE."'"

If you take a close look to following sub query :

                    SELECT t.post_id, 
                           MAX(t.schedule_start) AS schedule_start, 
                           MAX(t.schedule_next_payment) AS schedule_next_payment
                    FROM (
                        SELECT 
                                post_id,
                                case when pm.meta_key = '_schedule_start' then pm.meta_value else 0 end AS schedule_start,
                                case when pm.meta_key = '_schedule_next_payment' then pm.meta_value else 0 end AS schedule_next_payment
                                FROM (SELECT * FROM `$wpdb->postmeta` pm where pm.meta_key IN ('_schedule_next_payment', '_schedule_start')) AS pm
                        ) AS t
                    GROUP BY t.post_id

You can see that I changed postmeta table columns and row (rotate table) the I could easily join it with my posts table and order based on these two date fields.

Wordpress WP_List_Table sort data query problem

1 Answers1