plugins - How do I add a sortable column to the WooCommerce (hpos) subscriptions overview in WP Admin?

Closed. This question is off-topic. It is not currently accepting answers.

---

Your question should be specific to WordPress. Generic PHP/JS/SQL/HTML/CSS questions might be better asked at Stack Overflow or another appropriate Stack Exchange network site. Third-party plugins and themes are off-topic for this site; they are better asked about at their developers' support routes.

Closed 8 days ago.

Improve this question

Needing help with creating a sortable column for the Subscriptions table in WP Admin with HPOS (COT) enabled

I’ve added the column with “woocommerce_shop_subscription_list_table_columns”, I’ve added the column content with “woocommerce_shop_subscription_list_table_custom_column”, and I’ve got the filter “woocommerce_shop_subscription_list_table_sortable_columns” to make it sortable.

But now I’m stuck on how to change the ‘orderby’. In CPT I used “pre_get_posts”. I tried using “woocommerce_shop_subscription_list_table_prepare_items_query_args” but that results in an Inner Join which means that subscriptions without this meta_key are missing. It should be possible. WCS itself runs this query to sort on schedule_next_payment, with a left join:

SELECT wp_wc_orders.id FROM wp_wc_orders INNER JOIN wp_wc_orders_meta AS meta0 ON ( wp_wc_orders.id = meta0.order_id ) LEFT JOIN wp_wc_orders_meta AS wcs_meta_ac0 ON wcs_meta_ac0.order_id = wp_wc_orders.id AND wcs_meta_ac0.meta_key = '_schedule_next_payment' WHERE 1=1 AND (wp_wc_orders.status IN ('wc-pending','wc-active','wc-on-hold','wc-cancelled','wc-switched','wc-expired','wc-pending-cancel')) AND (wp_wc_orders.type = 'shop_subscription') AND ((meta0.meta_key = '_schedule_next_payment')) GROUP BY wp_wc_orders.id ORDER BY CAST( wcs_meta_ac0.meta_value AS DATETIME ) DESC, wp_wc_orders.id ASC LIMIT 0, 20

but my query is showing just with an inner join:

SELECT wp_wc_orders.id FROM wp_wc_orders INNER JOIN wp_wc_orders_meta AS meta0 ON ( wp_wc_orders.id = meta0.order_id ) WHERE 1=1 AND (wp_wc_orders.status IN ('wc-pending','wc-active','wc-on-hold','wc-cancelled','wc-switched','wc-expired','wc-pending-cancel')) AND (wp_wc_orders.type = 'shop_subscription') AND ((meta0.meta_key = 'my_key')) GROUP BY wp_wc_orders.id ORDER BY meta0.meta_value+0 ASC LIMIT 0, 20

Any WCS ninjas around who can help me understand how I correctly make the column sortable? Ideally I want the “official WCS” way, not a hack.