Add sorting to "code" column in WooCommerce coupon list

Question

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?

Answer 1

$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 );

---

Related: How to make the "coupon amount" column sortable in WooCommerce