WooCommerce: How to make the orders table sortable by shipping method?

In the previous article I’ve shared with you how you can add the shipping method and payment method columns to the orders table. Now I’m going to explain you how the shipping method column can be sortable.

First, we need to add the shipping method to the sortable field’s list. You can do this by attaching a new function to the manage_edit-shop_order_sortable_columns filter.

function customize_sort_function_custom_values( $columns ) {
	global $wpdb;

    $custom = array(
        'shipping_method' => 'shipping_method'
    );

    return wp_parse_args( $custom, $columns );
}

add_filter( "manage_edit-shop_order_sortable_columns", 'customize_sort_function_custom_values' );

Don’t worry about the shipping_method value, which is not a real field in the sql query. We’ll take care of it soon!
Since the shipping method won’t shop up in the query’s result (the base query here asks data only from the wp_posts and wp_post_meta tables) we need to join two additional tables: woocommerce_order_items and woocommerce_order_itemmeta. The woocommerce_order_items table, as the name shows, contains the items that have been ordered and the woocommerce_order_itemmeta contains meta data about the ordered items, such as shipping method. Fortunately to alter the main query there’s an easy way, which is the posts_join filter. Let’s set together the joins:

function order_by_shipping_method_order_join($query) {
    global $wpdb;

    if ( is_admin() && $_GET['post_type'] === 'shop_order' && $_GET['orderby'] === 'shipping_method' ) {
    	$first_item_in_order = "SELECT order_item_id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id = $wpdb->posts.ID AND order_item_type = 'shipping' LIMIT 0, 1";
        $query .= "LEFT JOIN {$wpdb->prefix}woocommerce_order_items AS items ON $wpdb->posts.ID = items.order_id AND items.order_item_id = ($first_item_in_order) ";
        $query .= "LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id AND (itemmeta.meta_key = 'method_id') ";
    }

    return $query;
}

add_filter('posts_join', 'order_by_shipping_method_order_join');

Of course we would like to alter the main query on the orders admin page and only in case when the sort field is the shipping_method. As you can see we need to use a sub-query to fetch the first item in the order, because we’ll use its shipping method as the order’s “main” shipping method. (this is what I’ve emphasized in the beginning of the article that it works only with one shipping method per order)
After the additional tables are joined, we need to filter the results for two reasons:

1. I don’t know why is that, but in the main query’s where part, instead of filtering for shop_order post type, it filters for shop_webhook.
2. Since we joined the woocommerce_order_itemmeta table, the number of results now equals the real number of orders multiplied by the number of meta data attached to every order item.

So let’s filter the results with the posts_where filter:

function order_by_shipping_method_where($where) {
	global $wpdb;

	if( is_admin() && $_GET['post_type'] === 'shop_order' && $_GET['orderby'] === 'shipping_method' ) {
		if(strpos($where, 'shop_webhook')) {
			return " AND $wpdb->posts.post_type = 'shop_order' AND itemmeta.meta_key = 'method_id'";
		}
	}

	return $where;
}

add_filter('posts_where', 'order_by_shipping_method_where');

We’re almost done, but one important thing remains. In the woocommerce_order_itemmeta table the shipping method is stored by its ID, not by the method label. So for example instead of Local Pickup you’ll see local_pickup. Maybe it works with the default labels (if you don’t change them) and in English, but it will cause a bad sorting if you use a localized version of WooCommerce. To solve this we have to define an ORDER BY clause in the SQL query.

function generate_replace_part($shipping_methods, $replace) {
	$key = array_shift(array_keys($shipping_methods));
	$value = array_shift($shipping_methods);

	$query_part = "REPLACE($replace, '$key', '$value')";

	if(!empty($shipping_methods)) {
		return generate_replace_part($shipping_methods, $query_part);
	}

	return $query_part;
}

function order_by_shipping_method_order($order_by) {
	global $wpdb;

	$shipping_methods = array(
		'local_delivery' => 'Házhozszállítás az általad megadott szállítási címre',
		'local_pickup'   => 'Személyes átvétel',
		'pick_pack_pont' => 'Pick Pack Pont'
	);

	if( is_admin() && $_GET['post_type'] === 'shop_order' && $_GET['orderby'] === 'shipping_method' ) {
		return generate_replace_part($shipping_methods, 'itemmeta.meta_value') . " " . strtoupper($_GET['order']);
	}
	
	return $order_by;
}

add_filter('posts_orderby', 'order_by_shipping_method_order');

Luckily the MySQL is able to execute transformations on fields before doing the sort, thus we’ll utilize it. In the shipping_methods array I listed some method IDs and the localized label for them. You can improve this by fetching methods from the WooCommerce. To be honest, it’s a bit tricky, becuase when this filter runs the woocoomerce is not loaded yet and I had no time to solve this.

So, that’s all. A bit tricky, but working! 🙂