How to check how many times a product has been purchased by a customer

Question

In woocommerce, how to check how many times a product has been purchased by a customer throughout an order.

How can I check how many times a product is bought by a customer throughout an order.

An example of a current customer’s product purchase history:

Product one = bought 5 times
Product five = bought 1 times
Product four = bought 2 times
Product two = bought 3 times
Product three = bought 6 times

I have a function to check whether this product is bought by a customer or not

function is_bought_items() {
 $bought = false;
 $_options = get_option( 'license_page_option_name' );
 $ex_product_ids = $_options['ex_product_ids_warranty']; 

 $targeted_products= explode(",",$ex_product_ids); //id array(4,17,28,52)

// Get all customer orders
$customer_orders = get_posts( array(
    'numberposts' => -1,
    'meta_key'    => '_customer_user',
    'meta_value'  => get_current_user_id(),
    'post_type'   => 'shop_order', // WC orders post type
    'post_status' => 'wc-completed' // Only orders with status "completed"
) );

// Going through each current customer orders
foreach ( $customer_orders as $customer_order ) {
    $order = wc_get_order( $customer_order );
    $order_id = $order->id;
    $items = $order->get_items();
    // Going through each current customer products bought in the order
    foreach ($items as $item) {
        // Your condition related to your 2 specific products Ids
        if ( in_array( $item['product_id'], $targeted_products) ) {
            $bought = true; // 
        }
    }
}

// return "true" if one the specifics products have been bought before by customer
if ( $bought ) {
    return true;
}
}

Answer 1

When navigating through all the elements ... we can save the product purchase countin our array from $item['item_meta']['_qty'].

Here is an example of a possible implementation ...

function get_purchased_products() {
    $products = array();

    // Get all customer orders
    $customer_orders = get_posts( array(
        'numberposts' => - 1,
        'meta_key'    => '_customer_user',
        'meta_value'  => get_current_user_id(),
        'post_type'   => 'shop_order', // WC orders post type
        'post_status' => 'wc-completed' // Only orders with status "completed"
    ) );

    // Going through each current customer orders
    foreach ( $customer_orders as $customer_order ) {
        $order    = wc_get_order( $customer_order );
        $items    = $order->get_items();

        // Going through each current customer products bought in the order
        foreach ( $items as $item ) {
            $id = $item['product_id'];

            // If product not in array, add it
            if ( ! array_key_exists( $item['product_id'], $products ) ) {
                $products[ $id ] = array(
                    'name' => $item['name'],
                    'count' => 0,
                );
            }

            // Increment Product `count` from cart quantity
            $products[ $id ]['count'] += $item['item_meta']['_qty'][0];
        }
    }

    return $products;
}
foreach ( get_purchased_products() as $id => $product ) {
    echo "<p>$id <b>$product[name]</b> bought $product[count] times</p>";
}

These outputs ...

70 7
37 5
19 1

Answer 2

, github https://github.com/woocommerce/woocommerce/blob/master/includes/wc-user-functions.php#L227-L238

MySQL :

$result = $wpdb->get_col( "
            SELECT im.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-completed', 'wc-processing' )
            AND pm.meta_key IN ( '_billing_email', '_customer_user' )
            AND im.meta_key IN ( '_product_id', '_variation_id' )
            AND im.meta_value != 0
            AND pm.meta_value IN ( '" . implode( "','", $customer_data ) . "' )
        " );