WooCommerce my-accounts Page - get the current user for orders query

Question

I'm trying to sum specific items for each user, but it seems it doesn't recognize the current user and it sums all the orders for all customers.

How can I solve this? What I am missing?

Here's the code I am using:

$order_items = apply_filters( 'woocommerce_reports_top_earners_order_items', $wpdb->get_results( "

SELECT order_item_meta_2.meta_value as product_id, SUM( order_item_meta.meta_value ) as line_total FROM {$wpdb->prefix}woocommerce_order_items as order_items

LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id

LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id

LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID

WHERE posts.post_type = 'shop_order'

AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )

AND order_items.order_item_type = 'line_item'

AND order_item_meta.meta_key = '_line_total'

AND order_item_meta_2.meta_key = '_product_id'

GROUP BY order_item_meta_2.meta_value

" ));
$totalPR = 0;
$Products = array(1507, 1406, 1506);

foreach ($order_items as $item) {

 if (in_array($item->product_id, $Products)) {

        $totalPR = $item->line_total + $totalPR;
         echo $totalPR;

    }

}

What exactly do you mean with "doesn't recognise the said user"? Do you get an error? Unexpected output? Something else? Please [edit] the question to include as much information as possible! — Martin Tournoij, Aug 14 '16 at 21:21

LoicTheAztec · Answer 1 · 2017-08-16T03:46:01.970

Update 2 - It's working making this:

Get the list of the customers
Go through each customer in a foreach loop
Get the customer ID

Add a LEFT JOIN for wp_postmeta table on the post_id:

LEFT JOIN {$wpdb->postmeta} AS postmeta ON order_items.order_id = postmeta.post_id

insert this 2 lines into your WHERE:

AND postmeta.meta_key = '_customer_user'

AND postmeta.meta_value = '$customer_id'

Then comes your loop inside the customers loop, to get the sum by customer

This is the code:

global $wpdb;

// Set here your product ids
$products = array( 1507, 1406, 1506 );
$all_customers = get_users( 'role=customer' );

foreach ( $all_customers as $customer ) {
    $customer_id = $customer->ID;
    $total_pr = 0;

    $query = $wpdb->get_results("

        SELECT order_item_meta_2.meta_value as product_id,

        SUM( order_item_meta.meta_value ) as line_total

        FROM {$wpdb->prefix}woocommerce_order_items as order_items

        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id

        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id

        LEFT JOIN {$wpdb->postmeta} AS postmeta ON order_items.order_id = postmeta.post_id

        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID

        WHERE posts.post_type = 'shop_order'

        AND posts.post_status IN ( '" . implode( "','", array( 'wc-completed', 'wc-processing', 'wc-on-hold' ) ) . "' )

        AND postmeta.meta_key = '_customer_user'

        AND postmeta.meta_value = '$customer_id'

        AND order_items.order_item_type = 'line_item'

        AND order_item_meta.meta_key = '_line_total'

        AND order_item_meta_2.meta_key = '_product_id'

        GROUP BY order_item_meta_2.meta_value

    ");

    $results = apply_filters( 'woocommerce_reports_top_earners_order_items', $query );

    foreach ( $results as $values ) {
         if ( in_array( $values->product_id, $products ) ) {
             $total_pr += $values->line_total;
        }
    }
    echo 'customer ID: ' . $customer->ID . ' | Total PR: ' . number_format( $total_pr, 2 ) . '<br>';
}

Tested and works. It will output the list with the Customer IDs and the corresponding Total PR.

References:

@PatrickJosephAragones On year after, **It works !!!** but this is too late :) … I just wanted to find the solution, so now is done. The answer was to look for the customer ID in the `wp_postmeta` table and not in the `wp_posts` table. See you around… — LoicTheAztec, Aug 16 '17 at 03:47

score 0 · Answer 2 · answered Mar 29 '22 at 04:33

$current_user = wp_get_current_user();
$current_user_id = $current_user->ID;
$args = array(
            'customer_id' => $current_user_id
        );
$orders = wc_get_orders($args);

You Can Use wp_get_current_user(); for Get Current User And Get Order Data With Args May be It's Work For You.$current_user_id = $current_user->ID;echo $current_user_id;//check current user id get or notecho $current_user_id;//check current user id get or not

WooCommerce my-accounts Page - get the current user for orders query

2 Answers2