how to make custom column in woo products view sortable?-CodePudding

I'm trying to add new new custom column in WooCommerce products list view.

I have the column created and data populated, however I'm having trouble making it sortable?

How can I make this custom column sortable in the table list?

Thanks

// Add new column to Woo Products Admin Dash

function woo_product_rmreference_column( $columns ) {
    $columns['rm_reference'] = esc_html__( 'Reference ID', 'woocommerce' );
        return $columns;
}
add_filter( 'manage_edit-product_columns', 'woo_product_rmreference_column', 2 );

// Populate column

function woo_product_rmreference_column_data( $column ) {
    global $post;

    if ( $column == 'rm_reference' ) {
            
            // Product
            $product_id = method_exists( $product, 'get_id' ) ? $product->get_id() : $product->id;
            
            // Get ACF Fields
            $reference = get_field( 'rightmove_property_reference', $product_id );

            // Output
            echo ($reference) ? '<div>'.$reference.'</div>';
    }
}
add_action( 'manage_product_posts_custom_column' ,

// Make Sortable

function product_rmreference_column_width_query( $query )
    {
        $orderby = $query->get( 'orderby' );
        if ( 'rm_reference' == $orderby ) {
            $meta_query = array(
                'relation' => 'OR',
                array(
                    'key' => '_ref',
                    'compare' => '>', 
                ),
                array(
                    'key' => '_ref',
                ),
            );

            $query->set( 'meta_query', $meta_query );
            $query->set( 'orderby', 'meta_value' );
        }
    }
add_action( 'pre_get_posts', 'product_rmreference_column_width_query' );

CodePudding user response：

You still need to add the column as sortable in the admin. I would also then make your pre_get_posts query evaluate _ref as NUMERIC

// ADD THIS.
add_filter( 'manage_edit-product_sortable_columns', 'my_sortable_reference_column' );
function my_sortable_reference_column( $columns ) {
    $columns['rm_reference'] = 'reference_id';
    return $columns;
}

function product_rmreference_column_width_query( $query ) {
    if ( is_admin() ) {
        $orderby = $query->get( 'orderby' );
        // I changed this to 'reference_id' to match the column sort.
        if ( 'reference_id' == $orderby ) {
            $meta_query = array(
                'relation' => 'OR',
                array(
                    'key'     => '_ref',
                    'compare' => '>',
                    'type' => 'NUMERIC'
                ),
                array(
                    'key' => '_ref',
                ),
            );
            
            $query->set( 'meta_query', $meta_query );
            $query->set( 'orderby', 'meta_value' );
        }
    }
}

add_action( 'pre_get_posts', 'product_rmreference_column_width_query' );