Get user last purchase date from specific product in WooCommerce

Question

I have faced a problem. I need to get a specific product purchase date using the user id and product id. Using "Checking if customer has already bought something in WooCommerce" answer code, it checks all product in the following:

function has_bought() {
    $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"
        ) 
    );
   return count($customer_orders) > 0 ? true : false; 
}
if( has_bought() )
    echo '<p>You have already maid a purchase</p>';
else
    echo '<p>Welcome, for your first purchase you will get a discount of 10%</p>';

What I need is to check specific product purchase data using the user id and product id.

How can I achieve this?

Answer 1

2024 Update for High Performance Order Storage (HPOS) and compatibility mode.

Using a direct custom lighter and effective SQL Query, will avoid looping through order items to find out the targeted product, saving server resources.

The query checks all product types, including variable products and product variations too.

For the date, we use the order creation date on processing and completed orders.

The function will return the last purchase date, otherwise it will return NULL.

1). For High Performance Order Storage (HPOS) or compatibility mode enabled, use the following with an even more lightweight optimized SQL query:

function get_product_purchased_last_date( $product_id, $user_id ) {
    global $wpdb;

    return $wpdb->get_var( $wpdb->prepare( "
        SELECT opl.date_created FROM {$wpdb->prefix}wc_order_product_lookup opl
        LEFT JOIN {$wpdb->prefix}wc_orders o ON opl.order_id = o.id
        WHERE o.type = 'shop_order' AND o.status IN ('wc-processing','wc-completed')
        AND o.customer_id = '%d' AND ( opl.product_id = %d OR opl.variation_id = %d )
        ORDER BY opl.order_id DESC LIMIT 1
    ", $user_id, $product_id, $product_id ) );
}

2). If you are using WordPress posts storage (legacy) with no compatibility mode enabled, use the following instead (original code):

function get_product_purchased_last_date( $product_id, $user_id ) {
    global $wpdb;

    return $wpdb->get_var( $wpdb->prepare( "
        SELECT p.post_date FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_id = p.ID
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
        WHERE p.post_type = 'shop_order' AND p.post_status IN ('wc-processing','wc-completed')
        AND pm.meta_key = '_customer_user' AND pm.meta_value = '%d'
        AND oim.meta_key IN ('_product_id','_variation_id') AND oim.meta_value = '%d'
        ORDER BY p.ID DESC LIMIT 1
    ", $user_id, $product_id ) );
}

The code goes in functions.php file of your child theme (or in a plugin). Tested and works.

---

USAGE example (on current product page, for the current user Id):

if( is_user_logged_in() ) {
    if( $date = get_product_purchased_last_date( get_the_id(), get_current_user_id() ) ) {
        $text = sprintf( __("You have already purchased this product on %s"), date( "jS \of F Y", strtotime($date) ) );
    } else {
        $text =  __("Welcome, for your first purchase you will get a discount of 10%");
    }
    // Display
    echo '<p>' . $text . '</p>';
}

---

Regarding settings for High Performance Order Storage, you can find them in:
WooCommerce > Settings > Advanced > Features