[Resolved] Filter WooCommerce orders by the product that was ordered

[Author]

Posts

[pascalR-5]

Hi,

I have a small WooCommerce store where people can order tickets to participate in a competition. For each competition, I want to create a page with a list of participants. I'm doing this by creating a view that displays WooCommerce orders in front-end pages. Is it possible to filter this query based on the product that was ordered?

I'm currently working around this by adding a custom checkbox group field to the Order post type, and then manually going through each order to assign them to the correct competition(s).

I also tried to create a many-to-many relationship between Order and Product, but this did not work for existing orders.

Best regards,
Guus Frenken

[Waqar]

Hi,

Welcome to Toolset support and I'd be happy to assist.

Since there is no built-in query filter available to filter the WooCommerce orders by the product, it will require some code customization.

Your approach of using a many-to-many relationship seems like a good option to join 'Orders' and "Products" posts, in a way that can be used for filtering the results in views.

a). For new orders:

You can use WooCommerce's hook "woocommerce_order_status_completed" ( ref: hidden link ) to execute a custom function that can get all the "products" from an "order" when it is completed, and then connect each product to the order, in the many-to-many relationship, using the "toolset_connect_posts" function.
( ref: https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/#toolset_connect_posts )

As a result, whenever a new order will be completed, a relationship connection will be created automatically between that "Order" post and the "Product" posts included in that order.

b). For existing order:

For "Orders" which are already completed (existing orders), you'll need another custom function to run one time, that can loop through all the order posts, get the IDs of the products included in an order and then connect them with that order post, in a relationship connection.

I hope this helps and for more personalized assistance around custom code, you can consider hiring a professional from our list of recommended contractors:
https://toolset.com/contractors/

regards,
Waqar

[pascalR-5]

Hi Waqar,

Thank you for the help, I was able to figure it out using your advice.

Here is my code in case anyone runs into a similar issue. This code assumes your many-to-many relationship's slug is order-product

Quick and dirty one-time-use code to connect existing orders:

$loop = new WP_Query( array(
	'post_type' => 'shop_order',
	'post_status' => array_keys( wc_get_order_statuses() ),
	'posts_per_page' => -1,
) );

$orders = $loop->posts;

foreach ( $orders as $order ) {
	$order = wc_get_order( $order->ID );

	foreach ( $order->get_items() as $item_id => $item ) {
		toolset_connect_posts( 'order-product', $order_id, $item->get_product_id() );
	}
}

Connect new orders using woocommerce_checkout_order_processed hook:

add_action( 'woocommerce_checkout_order_processed', 'connectOrder', 1, 1 );

function connectOrder( $order_id ) {
    $order = wc_get_order( $order_id );

    foreach ( $order->get_items() as $item_id => $item ) {
        toolset_connect_posts( 'order-product', $order_id, $item->get_product_id() );
    }
}