CODEWITHSUNDEEP
phpsqlwordpresswoocommerceproduct
beig
beig

Reputation: 55

Display Woocommerce purchased simple products by customer

I am displaying the purchased products in their account page by user logged-in. I implemented it using the instructions on this link Woocommerce List All Previously Ordered Products. I'd like to only display the purchased products with product type "simple" instead of displaying all purchased products.

I used the below query:

global $wpdb;

$current_user   = wp_get_current_user();
$customer_email = $current_user->user_email;
$customer_data = array( $customer_email );
$statuses      = array_map( 'esc_sql', wc_get_is_paid_statuses() );

$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-" . implode( "','wc-", $statuses ) . "' )
    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 ) . "' )
" );

What will be the right sql query to be inserted to it in order to display only the products with product type "simple"?

Upvotes: 1

Views: 634

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 253869

It is possible to restrict this query to simple product only with the following code, Where we do a sub "taxonomy query" based on product_type Woocommerce taxonomy (that manage the product types) for the term slug simple (simple products):

global $wpdb;

$current_user   = wp_get_current_user();
$customer_email = $current_user->user_email;
$customer_data = array( $customer_email );
$statuses      = array_map( 'esc_sql', wc_get_is_paid_statuses() );

$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-" . implode( "','wc-", $statuses ) . "' )
    AND pm.meta_key IN ( '_billing_email', '_customer_user' ) 
    AND im.meta_key = '_product_id'
    AND im.meta_value IN (
        SELECT tr.object_id 
        FROM {$wpdb->prefix}term_relationships as tr
        INNER JOIN {$wpdb->prefix}term_taxonomy as tt 
            ON tr.term_taxonomy_id = tt.term_taxonomy_id
        INNER JOIN {$wpdb->prefix}terms as t 
            ON tt.term_id = t.term_id
        WHERE tt.taxonomy = 'product_type' 
        AND t.slug = 'simple'
    )
    AND pm.meta_value IN ( '" . implode( "','", $customer_data ) . "' )
    GROUP BY im.meta_value
" );

// The raw output (testing
echo '<pre>'; print_r($result); echo '</pre>';

Tested and works.

Upvotes: 1

Related Questions