Add a sortable and searchable "customer country" column in WooCommerce admin order list-CodePudding

I added a "Customer Country" column to the WooCommerce orders table (in admin dashboard), but I can't figure how to make it sortable. Any help? It would be a nice bonus if it could be made also searchable.

My current code:

add_filter( 'manage_edit-shop_order_columns', function( $columns ) {
    $columns['customer_country'] = 'Customer Country';
    return $columns;
}, 10, 1 );

add_action( 'manage_shop_order_posts_custom_column', function( $column ) {
    global $post;
    if( 'customer_country' === $column ) {
        $order = wc_get_order( $post->ID );
        echo get_user_geo_country( $order->get_customer_ip_address() );
    }
}, 10, 1 );

add_filter( 'manage_edit-shop_order_sortable_columns', function( $columns ) {
    $columns['customer_country'] = 'customer_country';
    return $columns;
}, 10, 1 );

add_action( 'pre_get_posts', function( $query ) {
    if( ! is_admin() ) { return; }
    // ????
}, 10, 1 );

/** Get geolocated country name by IP **/
function get_user_geo_country( $user_ip ) {
    $geo      = new WC_Geolocation();              // Get WC_Geolocation instance object
    $user_geo = $geo->geolocate_ip( $user_ip );    // Get geolocated user data
    $country  = $user_geo['country'];              // Get the country code

    return WC()->countries->countries[ $country ]; // return the country name
}

CodePudding user response：

To make it sortable you can use the manage_edit-shop_order_sortable_columns in combination with the pre_get_posts hook.

To make it searchable you can use the woocommerce_shop_order_search_fields hook.

So you get:

// Get geolocated country code by IP
function get_user_geo_country( $user_ip ) {
    // Get WC_Geolocation instance object
    $geo = new WC_Geolocation();

    // Get geolocated user data 
    $user_geo = $geo->geolocate_ip( $user_ip );
    
    // Get the country code
    $country = $user_geo['country'];

    // Return the country code
    return $country;
}
 
// Display new column on WooCommerce admin orders list (header)
function filter_manage_edit_shop_order_columns( $columns ) {    
    $columns['customer_country'] = __( 'Customer Country', 'woocommerce' );
    
    return $columns;
}
add_filter( 'manage_edit-shop_order_columns', 'filter_manage_edit_shop_order_columns', 10, 1 );

// Display details after order status column, on order admin list (populate the column)
function action_manage_shop_order_posts_custom_column( $column, $post_id ) {    
    // Compare
    if ( $column == 'customer_country' ) {
        // Get order
        $order = wc_get_order( $post_id );
    
        // Get shipping country code
        $user_geo_country = get_user_geo_country( $order->get_customer_ip_address() );
        
        // NOT empty
        if ( ! empty ( $user_geo_country ) ) {
            echo $user_geo_country;
        } else {
            echo __( 'N/A', 'woocommerce' );
        }
    }
}
add_action( 'manage_shop_order_posts_custom_column' , 'action_manage_shop_order_posts_custom_column', 10, 2 );

// Make custom column sortable
function filter_manage_edit_shop_order_sortable_columns( $sortable_columns ) {  
    return wp_parse_args( array( 'customer_country' => '_shipping_country' ), $sortable_columns );
}
add_filter( 'manage_edit-shop_order_sortable_columns', 'filter_manage_edit_shop_order_sortable_columns', 10, 1 );

// Orderby for custom sortable column
function action_pre_get_posts( $query ) {   
    // If it is not admin area, exit
    if ( ! is_admin() ) return;
    
    global $pagenow;

    // Compare
    if ( $pagenow === 'edit.php' && isset( $_GET['post_type'] ) && $_GET['post_type'] === 'shop_order' ) {      
        // Get orderby
        $orderby = $query->get( 'orderby' );

        // Set query
        if ( $orderby == '_shipping_country' ) {           
            $query->set( 'meta_key', '_shipping_country' );
            $query->set( 'orderby', 'meta_value' );
        }
    }
}
add_action( 'pre_get_posts', 'action_pre_get_posts', 10, 1 );

// Make searchable
function filter_woocommerce_shop_order_search_fields( $meta_keys ) {    
    $meta_keys[] = '_shipping_country';
    return $meta_keys;
}
add_filter( 'woocommerce_shop_order_search_fields', 'filter_woocommerce_shop_order_search_fields', 10, 1 );

This works relatively easily. The only drawback is that you will not see the full country name, but only the country code. This is because the search and sort hook require a metakey. WooCommerce only stores the country code, as metadata for each order, and the full name of the country is not stored in the database and therefore cannot be used.

If you really want to go for the full country name. Then, in addition to the country code, you can also add the full country name as metadata for every new order, via the woocommerce_thankyou hook and $order->update_meta_data()

So then you get:

// Get geolocated country name by IP
function get_user_geo_country( $user_ip ) {
    // Get WC_Geolocation instance object
    $geo = new WC_Geolocation();

    // Get geolocated user data 
    $user_geo = $geo->geolocate_ip( $user_ip );
    
    // Get the country code
    $country = $user_geo['country'];

    // Return the country name
    return WC()->countries->countries[ $country ];
}
 
function action_woocommerce_thankyou( $order_id ) {     
    // Get $order object
    $order = wc_get_order( $order_id );
    
    // Is a WC_Order
    if ( is_a( $order, 'WC_Order' ) ) {
        // Get full shipping country name
        $user_geo_country = get_user_geo_country( $order->get_customer_ip_address() );

        // NOT empty
        if ( ! empty( $user_geo_country ) ) {
            // Update meta data
            $order->update_meta_data( '_shipping_country_full_name', $user_geo_country );
        } else {
            // NOT available
            $na = __( 'N/A', 'woocommerce' );

            // Update meta data         
            $order->update_meta_data( '_shipping_country_full_name', $na );
        }
        
        // Save
        $order->save();
    }
}
add_action( 'woocommerce_thankyou', 'action_woocommerce_thankyou', 10, 1 );
 
// Display new column on WooCommerce admin orders list (header)
function filter_manage_edit_shop_order_columns( $columns ) {    
    $columns['customer_country'] = __( 'Customer Country', 'woocommerce' );
    
    return $columns;
}
add_filter( 'manage_edit-shop_order_columns', 'filter_manage_edit_shop_order_columns', 10, 1 );

// Display details after order status column, on order admin list (populate the column)
function action_manage_shop_order_posts_custom_column( $column, $post_id ) {    
    // Compare
    if ( $column == 'customer_country' ) {
        // Get order
        $order = wc_get_order( $post_id );
        
        // Get meta
        $shipping_country_full_name = $order->get_meta( '_shipping_country_full_name' );

        // NOT empty
        if ( ! empty ( $shipping_country_full_name ) ) {
            echo $shipping_country_full_name;
        } else {
            echo __( 'N/A', 'woocommerce' );
        }
    }
}
add_action( 'manage_shop_order_posts_custom_column' , 'action_manage_shop_order_posts_custom_column', 10, 2 );

// Make custom column sortable
function filter_manage_edit_shop_order_sortable_columns( $sortable_columns ) {  
    return wp_parse_args( array( 'customer_country' => '_shipping_country_full_name' ), $sortable_columns );
}
add_filter( 'manage_edit-shop_order_sortable_columns', 'filter_manage_edit_shop_order_sortable_columns', 10, 1 );

// Orderby for custom sortable column
function action_pre_get_posts( $query ) {   
    // If it is not admin area, exit
    if ( ! is_admin() ) return;
    
    global $pagenow;

    // Compare
    if ( $pagenow === 'edit.php' && isset( $_GET['post_type'] ) && $_GET['post_type'] === 'shop_order' ) {      
        // Get orderby
        $orderby = $query->get( 'orderby' );

        // Set query
        if ( $orderby == '_shipping_country_full_name' ) {           
            $query->set( 'meta_key', '_shipping_country_full_name' );
            $query->set( 'orderby', 'meta_value' );
        }
    }
}
add_action( 'pre_get_posts', 'action_pre_get_posts', 10, 1 );

// Make searchable
function filter_woocommerce_shop_order_search_fields( $meta_keys ) {    
    $meta_keys[] = '_shipping_country_full_name';
    return $meta_keys;
}
add_filter( 'woocommerce_shop_order_search_fields', 'filter_woocommerce_shop_order_search_fields', 10, 1 );

The disadvantage of this is that the existing orders would not have that data, so you would have to add this for all those orders as well, which can then be added again with the following function:

function action_wp_footer() {
    $limit = 50;
    
    // Args
    $args = array(
        'limit'        => $limit,
        'meta_key'     => '_shipping_country_full_name',
        'meta_compare' => 'NOT EXISTS',
    );

    // Get orders
    $orders = wc_get_orders( $args );
    
    // NOT empty
    if ( ! empty ( $orders ) ) {
        // Loop through orders
        foreach ( $orders as $order ) {
            // Is a WC_Order
            if ( is_a( $order, 'WC_Order' ) ) {     
                // Get full shipping country name
                $user_geo_country = get_user_geo_country( $order->get_customer_ip_address() );
                
                // NOT empty
                if ( ! empty( $user_geo_country ) ) {
                    // Update meta data
                    $order->update_meta_data( '_shipping_country_full_name', $user_geo_country );
                } else {
                    // NOT available
                    $na = __( 'N/A', 'woocommerce' );

                    // Update meta data         
                    $order->update_meta_data( '_shipping_country_full_name', $na );
                }
                
                // Save
                $order->save();
            }
        }
    }
}
add_action( 'wp_footer', 'action_wp_footer' );

Each time you/a visitor will browse a front end page of your site the function will be triggered. Processing orders by 50 (limit) is just more secure and will avoid a timeout or errors. When all existing orders are updated, this function can be removed