Connection posts to shop_order (WooCommerce)

[btribouillet]

Hi,

Since shop_order use different status, the standard post_status is not used, but instead:

'wc-pending', 'wc-processing', 'wc-on-hold', 'wc-completed', 'wc-cancelled', 'wc-refunded' and 'wc-failed'.

The first thing i noticed is that my connection "shop_order_to_warehouse" (warehouse is a cpt) in admin order single page. Does not show my selected warehouse (but i can't select another one since i use a many-to-one cardinality.

Screenshot

Here my connection:

// Orders to Warehouse
p2p_register_connection_type(array(
    'name' => 'shop_order_to_warehouse',
    'cardinality' => 'many-to-one',
    'from' => 'shop_order',
    'to' => 'warehouse',
    'admin_box' => 'from'
));

So the second thingi noticed is that i query the connected cpt in admin orders list to add some extra data in the order table.

I managed to have the correct object from get_queried_object() but $connected is empty.

$connected = new WP_Query(array(
    'connected_type' => 'shop_order_to_warehouse',
    'connected_items' => $shop_order->get_queried_object(),
    'nopaging' => true
)); 

Generated SQL:

SELECT     wp_posts.*,
           wp_p2p.*
FROM       wp_posts
INNER JOIN wp_p2p
where      1=1
AND        wp_posts.post_type = 'warehouse'
AND        (
                      wp_posts.post_status = 'publish'
           OR         wp_posts.post_status = 'aw-disabled'
           OR         wp_posts.post_status = 'future'
           OR         wp_posts.post_status = 'draft'
           OR         wp_posts.post_status = 'pending'
           OR         wp_posts.post_status = 'private')
AND        (
                      wp_p2p.p2p_type = 'shop_order_to_warehouse'
           AND        wp_posts.id = wp_p2p.p2p_to
           AND        wp_p2p.p2p_from IN
                      (
                               SELECT   wp_posts.id
                               FROM     wp_posts
                               WHERE    1=1
                               AND      wp_posts.id IN (9705)
                               AND      wp_posts.post_type = 'shop_order'
                               AND      (
                                                 wp_posts.post_status = 'publish'
                                        OR       wp_posts.post_status = 'aw-disabled'
                                        OR       wp_posts.post_status = 'future'
                                        OR       wp_posts.post_status = 'draft'
                                        OR       wp_posts.post_status = 'pending'
                                        OR       wp_posts.post_status = 'private')
                               ORDER BY wp_posts.post_date DESC ))
ORDER BY   wp_posts.post_date DESC

I tried passing the wc status in the connected WP_Query

$connected = new WP_Query(array(
    'connected_type' => 'shop_order_to_warehouse',
    'connected_items' => $shop_order->get_queried_object(),
    'nopaging' => true,
    'post_status' => array( 'wc-pending', 'wc-processing', 'wc-on-hold', 'wc-completed', 'wc-cancelled', 'wc-refunded', 'wc-failed' )
));

Generated SQL:

SELECT     wp_posts.*,
           wp_p2p.*
FROM       wp_posts
INNER JOIN wp_p2p
where      1=1
AND        wp_posts.post_type = 'warehouse'
AND        ((
                                 wp_posts.post_status = 'wc-pending'
                      OR         wp_posts.post_status = 'wc-processing'
                      OR         wp_posts.post_status = 'wc-on-hold'
                      OR         wp_posts.post_status = 'wc-completed'
                      OR         wp_posts.post_status = 'wc-cancelled'
                      OR         wp_posts.post_status = 'wc-refunded'
                      OR         wp_posts.post_status = 'wc-failed'))
AND        (
                      wp_p2p.p2p_type = 'shop_order_to_warehouse'
           AND        wp_posts.id = wp_p2p.p2p_to
           AND        wp_p2p.p2p_from IN
                      (
                               SELECT   wp_posts.id
                               FROM     wp_posts
                               WHERE    1=1
                               AND      wp_posts.id IN (9705)
                               AND      wp_posts.post_type = 'shop_order'
                               AND      (
                                                 wp_posts.post_status = 'publish'
                                        OR       wp_posts.post_status = 'aw-disabled'
                                        OR       wp_posts.post_status = 'future'
                                        OR       wp_posts.post_status = 'draft'
                                        OR       wp_posts.post_status = 'pending'
                                        OR       wp_posts.post_status = 'private')
                               ORDER BY wp_posts.post_date DESC ))
ORDER BY   wp_posts.post_date DESC

As we can see it does not use the correct post_status since shop_order use different status.

The correct SQL (tested) should be :

SELECT     wp_posts.*,
           wp_p2p.*
FROM       wp_posts
INNER JOIN wp_p2p
where      1=1
AND        wp_posts.post_type = 'warehouse'
AND        (
                      wp_posts.post_status = 'publish'
           OR         wp_posts.post_status = 'aw-disabled'
           OR         wp_posts.post_status = 'future'
           OR         wp_posts.post_status = 'draft'
           OR         wp_posts.post_status = 'pending'
           OR         wp_posts.post_status = 'private')
AND        (
                      wp_p2p.p2p_type = 'shop_order_to_warehouse'
           AND        wp_posts.id = wp_p2p.p2p_to
           AND        wp_p2p.p2p_from IN
                      (
                               SELECT   wp_posts.id
                               FROM     wp_posts
                               WHERE    1=1
                               AND      wp_posts.id IN (9705)
                               AND      wp_posts.post_type = 'shop_order'
                               AND      (
                                                 wp_posts.post_status = 'wc-pending'
                                        OR       wp_posts.post_status = 'wc-processing'
                                        OR       wp_posts.post_status = 'wc-on-hold'
                                        OR       wp_posts.post_status = 'wc-completed'
                                        OR       wp_posts.post_status = 'wc-cancelled'
                                        OR       wp_posts.post_status = 'wc-refunded'
                                   		OR       wp_posts.post_status = 'wc-failed')
                               ORDER BY wp_posts.post_date DESC ))
ORDER BY   wp_posts.post_date DESC

[btribouillet]

I ended up using posts_where filter as a quick fix to change the post_status string.

function shop_order_where_statement( $where ) {
    $post_status_needle = "(wp_posts.post_status = 'publish' OR wp_posts.post_status = 'aw-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')";

    if( strpos( $where, "wp_posts.post_type = 'shop_order'" ) !== false && strpos( $where, $post_status_needle ) ) {
        $order_post_status = "(wp_posts.post_status = 'wc-pending' OR wp_posts.post_status = 'wc-processing' OR wp_posts.post_status = 'wc-on-hold' OR wp_posts.post_status = 'wc-completed' OR wp_posts.post_status = 'wc-cancelled' OR wp_posts.post_status = 'wc-refunded' OR wp_posts.post_status = 'wc-failed')";
        $where = str_replace( $post_status_needle, $order_post_status, $where );
    }

    //removes the actions hooked on the '__after_loop' (post navigation)
    remove_all_actions ( '__after_loop');

    return $where;
}
add_filter( 'posts_where' , 'shop_order_where_statement' );

But I think this is still an issue, the quick fix seems a little bit dirty in my opinion.

Any advises for a proper solution or is it really an issue?