[Resolved] List all orders related to a product

[Author]

Posts

[Pat]

Hello,

I'm using WC + Toolset on a site and would like to list all clients having bought this product. The needed info are firstname, lastname, mail and some other userfields. The Views will be on the page than the related product.
How can I manage my Views in order to get this?
Regards
Pat

[Pat]

Just another info : the products are variable products and I need to list also what variation have been chosen for each order.
Regards
Pat

[Jamal]

Hello Pat and thank you for contacting Toolset support.

Theoretically, this cannot be achieved with, only, Toolset views. For the simple reason that WooCommerce, now, uses its own tables to store order items. To achieve this, we'll need to get the order items related to the product, then get the orders from the order items, then get the customers from the orders.

To achieve this with Toolset, you will need to implement some custom code to be able to access the WooCommerce tables. Or to use the WooCommerce functions. Then, you can pass the IDs or emails of the customers to a Toolset users' view, or get the orders IDs and pass it to a Toolset posts view, then pull the customers' data from it, but that is not as efficient as a users' view.

First, I would suggest checking this plugin, if it does what you need, you should use it. https://wordpress.org/plugins/wc-product-customer-list/

Otherwise, check this code snippet, it may give you some inspiration to build your own custom code hidden link

I hope this helps. Let me know if you have any questions.

[Pat]

Hi Jamal,

Thanks for the info.
First, I think the first proposal is not relevant as I need to manage with precision the way data will be displayed in the frontend. So, the plugin does not seem to offer this kind of feature !

Your second proposal is much more in line with what I'm searching for. If I understand well, I need to recover the list of user ID that have bought the specific product and then, I can use a Views to manage the output display.

The 2 issues I am facing with the proposed code are :
1. The snippet returns the mails of the users and not the ID
2. How can I link the snippet and the Views I will use to make the display on the product page?

Let me know.
Regards
Pat

[Jamal]

1) Both the billing email and the custom ID are saved in an order custom field. The customer ID is stored as "_customer_user". Adapt the code to use it.

2) Create a function from the snippet that will return an array of customers IDs, then hook into the view's query filter to add those IDs to it. For example:

function get_customers_by_product_id( $product_id){
    global $wpdb;
       
    // Find customers IDs in the DB order table
    $statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
    $customer_emails = $wpdb->get_col("
       SELECT DISTINCT pm.meta_value FROM {$wpdb->posts} AS p
       INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
       INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
       INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
       WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
       AND pm.meta_key IN ( '_customer_user' )
       AND im.meta_key IN ( '_product_id', '_variation_id' )
       AND im.meta_value = $product_id
    ");
}

add_filter( 'wpv_filter_user_query', 'include_customers_by_product_id', 99, 3 );

function include_customers_by_product_id( $query_args, $view_settings, $view_id){
    global $product;
    if ( $view_id == 123 ){
        $query_args['include'] => get_customers_by_product_id( $product->ID );
    }
    return $query_args;
}

Please note that the wpv_filter_user_query is for users views. Users views are not yet available within the blocks editor. And that the code will only run for the view with ID 123.
https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_user_query

Also, please test this code, I did not test it from my side, because it is basically around WooCommerce rather than Toolset. Check our support policy https://toolset.com/toolset-support-policy/

[Pat]

Hi Jamal,

Thanks for the info.
I have created the function (Toolset parameters) and have defined a new user Views (ID : 20561). This Views is placed in the product template (the one which is used to display all simple products).
The issue is that I'm getting ALL users in the output of this Views (and not only those which have placed an order for the specific product).
For the moment, I have not defined in the Views any filter (as normally, the function should make it?). I have also indicate in the function the Views ID (20561)
Any idea on what could be missing?

Regards
Pat

[Pat]

Jamal,

For info, I'm getting an error with the code :

[2021-06-29 15:28:03, ajax] syntax error, unexpected '=>' (T_DOUBLE_ARROW) in toolset-customizations/liste-cdes-liees-produit.php on line 24

A problem occurred when executing snippet "liste-cdes-liees-produit". The result of include_once is: ""

Hope this helps to find the issue.
Regards
Pat

[Jamal]

Hello Pat,

Actually, there is an error at line 24 in the code I suggested, it should be "=" instead of "=>". And we'll need to return the IDs of the users.

First of all, make sure that the function "get_customers_by_product_id" returns the correct users/customers. You can use a shortcode to check it on the content template:

add_shortcode('product-buyers-ids', 'get_customers_by_product_id');

And test it inside the content template with:

IDs of the customers that have bought this product: [product-buyers-ids]

Try this code:

function get_customers_by_product_id( $product_id){
    global $wpdb;
        
    // Find customers IDs in the DB order table
    $statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
    $customer_ids = $wpdb->get_col("
       SELECT DISTINCT pm.meta_value FROM {$wpdb->posts} AS p
       INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
       INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
       INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
       WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
       AND pm.meta_key IN ( '_customer_user' )
       AND im.meta_key IN ( '_product_id', '_variation_id' )
       AND im.meta_value = $product_id
    ");
    return = $customer_ids;
}

// You can remove the following shortcode definition once you complete the code
add_shortcode('product-buyers-ids', 'get_customers_by_product_id');
 
add_filter( 'wpv_filter_user_query', 'include_customers_by_product_id', 99, 3 );
 
function include_customers_by_product_id( $query_args, $view_settings, $view_id){
    global $product;
    if ( $view_id == 123 ){
        $query_args['include'] = get_customers_by_product_id( $product->ID );
    }
    return $query_args;
}

[Pat]

Hi Jamal,

First, I was still getting an error. So, I have modified the line return = $customer_ids; by return $customer_ids;
No, no more error reported by Toolset.
I have also placed the shortcode inside the product template. The result is : Array
So ir seems something is not working fine !
Let me know
Regards
Pat

[Jamal]

You are right, it should be return $customers; not return = $customers.

Regarding the shortcode, try this:

// You can remove the following shortcode definition once you complete the code
add_shortcode('product-buyers-ids', function(){
    global $product;
    $ids = get_customers_by_product_id( $product->ID );
    return implode( ", " , $ids );
});

[Pat]

Hi Jamal,

I just tested your solution. Nothing is displayed with the shortcode.
And the Views continues to list all users !
Regards
Pat

[Pat]

Hi Jamal,

For info, I have tried to replace the $product->ID by the product ID of the desired prodyuct in the shortcode, but the result is the same, meaning this is not the rootcause of the issue!
Regards
Pat

[Pat]

Hi Jamal,

Really sorry but I was getting the same "cache" issue than with the other tread I have posted some days ago.
I have refreshed the page and now, your code is running well.
Many thanks for your support
Pat

[Pat]

My issue is resolved now. Thank you!

[Pat]

Hi Jamal,

Sorry to reopen this ticket.
In addition to the customers IDs and other info (name, email, phone) that are working fine, I need also to list additional info :
1. Date of the purchase
2. Quantity of purchased product.
3. Product variant chosen (I'm working here with variable products)

This means that a customer could buy different quantities of product variant and we need to get this!
As currently, the function just list the user IDs, I'm not sure how to get the additional info and use it inside the current Views?

Many thanks
Pat