Skip to content

Get WooCommerce product purchase date from a product Id for a user Id

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( 
            '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>';
    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?



Using a direct custom SQL Query, avoiding looping through order items to find out the targeted product, will make the database query and the php process much more lighter and effective.

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

Now for the date here we use the order post date.

So here is the custom query embedded in a function (returns a date or an empty value):

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 = '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'
    ", $user_id, $product_id ) );

Code goes in function.php file of your active child theme (or active theme). 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 the %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>';
4 People found this is helpful