How to implement a filter by shipping method in woocommerce backend?

1 Views

I need to implement a filter in the woocommerce backend, that I can use to filter the orders by the selected shipping method.

I can create a filter on custom fields and alter the query, but the problem is that woocommerce stores the shipping method in a custom table of the DB.

Read More

Any hints on how to achieve this filter?

Post Views: 1

Related posts

Leave a Reply

4 comments

  1. I solved adding a dropdown menu, using this hook:

    add_action( 'restrict_manage_posts', 'display_shipping_dropdown' );

    And then used this other hook to extend the where clause:

    add_filter( 'posts_where', 'admin_shipping_filter', 10, 2 );
function admin_shipping_filter( $where, &$wp_query )
{
    global $pagenow;
    $method = $_GET['shipping_filter'];

    if ( is_admin() && $pagenow=='edit.php' && $wp_query->query_vars['post_type'] == 'shop_order' && !empty($method) ) {
        $where .= $GLOBALS['wpdb']->prepare( 'AND ID
                            IN (
                                SELECT order_id
                                FROM wp_woocommerce_order_items
                                WHERE order_item_type = "shipping"
                                AND order_item_name = "' . $method . '"
                            )' );
    }

    return $where;
}

  2. To complete Lorenzo’s answer, here is a function you can use to generate the filter html :

    function display_shipping_dropdown(){

    if (is_admin() && !empty($_GET['post_type']) && $_GET['post_type'] == 'shop_order'){

        $exp_types = array();

        $zones = WC_Shipping_Zones::get_zones();
        foreach($zones as $z) {
            foreach($z['shipping_methods'] as $method) {
                $exp_types[] = $method->title;
            }
        }

        ?>
        <select name="shipping_method">
            <option value=""><?php _e('Filter par expédition', 'woocommerce'); ?></option>
            <?php
            $current_v = isset($_GET['shipping_method']) ? $_GET['shipping_method'] : '';
            foreach ($exp_types as $label) {
                printf
                (
                    '<option value="%s"%s>%s</option>',
                    $label,
                    $label == $current_v? ' selected="selected"':'',
                    $label
                );
            }
            ?>
        </select>
        <?php
    }
}
add_action( 'restrict_manage_posts', 'display_shipping_dropdown' );

  3. in Lorenzo’s solution, the algorithm will fail if the title of the shipping method is changed. (in my case dynamically by a plugin).

    Using instance_id instead of title will avoid this.

    add_action( 'restrict_manage_posts', function () {
    if ( is_admin() && ! empty( $_GET['post_type'] ) && $_GET['post_type'] == 'shop_order' ) {
        $exp_types = array();
        $zones     = WC_Shipping_Zones::get_zones();
        foreach ( $zones as $z ) {
            foreach ( $z['shipping_methods'] as $method ) {
                $exp_types[ $method->instance_id ] = $method->title;
            }
        }
        ?>
        <select name="shipping_method">
            <option value=""><?php _e( 'Shipping filter' ); ?></option>
            <?php
            $current_v = isset( $_GET['shipping_method'] ) ? $_GET['shipping_method'] : '';
            foreach ( $exp_types as $key => $label ) {
                printf( '<option value="%s"%s>%s</option>', $key, $key == $current_v ? ' selected="selected"' : '', $label );
            }
            ?>
        </select>
        <?php
    }
} );

add_filter( 'posts_where', function ( $where, &$wp_query ) {
    global $pagenow, $wpdb;
    $method = isset( $_GET['shipping_method'] ) ? $_GET['shipping_method'] : false;

    if ( is_admin() && $pagenow == 'edit.php' && $wp_query->query_vars['post_type'] == 'shop_order' && ! empty( $method ) ) {
        $where .= $wpdb->prepare( "AND ID
IN (
 SELECT order_id
 FROM {$wpdb->prefix}woocommerce_order_itemmeta m
 LEFT JOIN {$wpdb->prefix}woocommerce_order_items i 
  ON i.order_item_id = m.order_item_id
 WHERE meta_key = 'instance_id' and meta_value = '{$method}' )" );
    }

    return $where;
}, 10, 2 );

  4. If you combine MrSwed and Lorenzo’s code, you get this (please correct me if I’m wrong but it works for me):

    if ( is_admin()) {
add_filter( 'posts_where', 'admin_shipping_filter', 10, 2 );
function admin_shipping_filter( $where, $wp_query )
{
global $pagenow;
$method = $_GET['shipping_method'];

if ( is_admin() && $pagenow=='edit.php' && $wp_query->query_vars['post_type'] == 'shop_order' && !empty($method) ) {
    $where .= $GLOBALS['wpdb']->prepare( 'AND ID
                        IN (
                            SELECT order_id
                            FROM wp_woocommerce_order_items
                            WHERE order_item_type = "shipping"
                            AND order_item_name = "' . $method . '"
                        )' );
}

return $where;
}
function display_shipping_dropdown(){

if (is_admin() && !empty($_GET['post_type']) && $_GET['post_type'] == 'shop_order'){

    $exp_types = array();

    $zones = WC_Shipping_Zones::get_zones();
    foreach($zones as $z) {
        foreach($z['shipping_methods'] as $method) {
            $exp_types[ $method->instance_id ] = $method->title;
        }
    }

    ?>
    <select name="shipping_method">
        <option value=""><?php _e('Filter Shipping', 'woocommerce'); ?></option>
        <?php
        $current_v = isset($_GET['shipping_method']) ? $_GET['shipping_method'] : '';
        foreach ($exp_types as $label) {
            printf
            (
                '<option value="%s"%s>%s</option>',
                $label,
                $label == $current_v? ' selected="selected"':'',
                $label
            );
        }
        ?>
    </select>
    <?php
    }
}
add_action( 'restrict_manage_posts', 'display_shipping_dropdown' );
}