Make custom column sortable by order of custom field value

• rosko34

• May 15, 2022 at 7:58 am

In my ACF field group, which is set up for Post Type -> Product. I have added a new field property_reference (Type = Text) to hold an individual reference number. I wanted to display this value in the Products table view for WooCommerce in my Admin WP Dashboard. So I have now created a custom column which outputs this value.

At the moment I only have three entries, these are BA001, BA002, BA003. However, before things get wild, I would like to be able to sort this custom column in order.

I have added some code shown below. This makes the Reference ID Column clickable, but provides ‘No products found’ line on the table.

So in order to make this sortable, to list in the order of the custom field property_reference from highest to lowest. How do I set up the custom field and make use of the meta query?

I should note I’m a little new to this and still trying to get my head around it 🙂

Any help or advice would be much appreciated. Thanks

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

• John Huebner

• May 15, 2022 at 11:09 am

You should be able to remove the meta_query and add

$query->set( 'meta_key', '_ref' );

• rosko34

• May 17, 2022 at 10:28 am

Thanks, John, but unfortunately still ‘No Products found’.

Noob question, but do I need to add _ref the meta_key in the field settings inside ACF? – Currently, it’s just a simple text based field.

Thanks

• John Huebner

• May 17, 2022 at 10:56 am

You do not want to filter the posts by anything, just set the orderby, meta_key and meta_type arguments. See this https://www.smashingmagazine.com/2017/12/customizing-admin-columns-wordpress/#making-columns-sortable

• rosko34

• May 17, 2022 at 11:10 am

Thanks John.

Got it, works a treat.

add_action( 'pre_get_posts', 'product_rmreference_column_orderby' );
function product_rmreference_column_orderby( $query ) {
  if( ! is_admin() || ! $query->is_main_query() ) {
    return;
  }

  if ( 'reference_id' === $query->get( 'orderby') ) {
	$query->set( 'orderby', 'meta_value' );
	$query->set( 'meta_key', 'reference_id' );
    $query->set( 'meta_type', 'numeric' );
  }
}