Add sorting to "code" column in WooCommerce coupon list-CodePudding

I am trying to alphabetically and numerically sort the coupon code admin column.

I have some coupons that is made up with only numbers and others that is only letters.

I would like to display:

All the numbers coupon codes in numerical order and then
All the letter coupon codes in alphabetical order

Based on How to make the "coupon amount" column sortable in WooCommerce answer code, this is my attempt:

function filter_manage_edit_shop_coupon_sortable_columns( $columns ) {
    // $columns['amount'] = 'amount';
    $columns['coupon_code'] = 'code';

    return $columns;
}
add_filter( 'manage_edit-shop_coupon_sortable_columns', 'filter_manage_edit_shop_coupon_sortable_columns', 10, 1 );

// Fires after the query variable object is created, but before the actual query is run.
function action_pre_get_posts( $query ) {
    // If it is not admin area, exit the filter immediately
    if( ! is_admin() ) return;

    // Get orderby
    $orderby = $query->get( 'orderby' );

    // Set query
    // if( $orderby == 'amount' ) {
        if( $orderby == 'coupon' ) {
        // $query->set( 'meta_key', 'coupon_amount' );
        $query->set( 'meta_key', 'coupon_code' );
        $query->set( 'orderby', 'meta_value_num' );
    }
}
add_action( 'pre_get_posts', 'action_pre_get_posts', 10, 1 );

While the coupon column is sortable, the actual sorting is not working as expected. Any advice?

CodePudding user response：

$query->set( 'meta_key', '..' ) won't work because it's not about a metakey. Instead you have to use a custom SQL query and you can use $query->set( 'post__in', .. )

So you get:

// Make column sortable
function filter_manage_edit_shop_coupon_sortable_columns( $columns ) {
    $columns['coupon_code'] = 'code';

    return $columns;
}
add_filter( 'manage_edit-shop_coupon_sortable_columns', 'filter_manage_edit_shop_coupon_sortable_columns', 10, 1 );

// Fires after the query variable object is created, but before the actual query is run.
function action_pre_get_posts( $query ) {
    global $pagenow, $post_type, $wpdb;

    // Only for coupons
    if ( $query->is_admin && $pagenow === 'edit.php' && $post_type === 'shop_coupon' && $query->get( 'orderby' ) == 'code' ) {
        // ASC OR DESC
        $asc_desc = $query->get( 'order' );

        // Get all coupon IDs
        $coupon_ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->prefix}posts WHERE post_type = 'shop_coupon' ORDER BY post_name $asc_desc" );

        // Set
        $query->set( 'post__in', $coupon_ids );
        $query->set( 'orderby', 'post__in' );
    }
}
add_action( 'pre_get_posts', 'action_pre_get_posts', 10, 1 );