WooCommerce我的帐户页面 - 获取当前用户的订单查询
我试图总结每个用户的具体项目,但它似乎无法识别当前用户,它汇总了所有客户的所有订单。WooCommerce我的帐户页面 - 获取当前用户的订单查询
我该如何解决这个问题?我错过了什么?
下面是我使用的代码:
$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;
}
}
更新2 - 它的工作使这个:
- 获取客户名单
- 在foreach遍历每个客户回路
- 取得客户编号
-
添加LEFT JOIN为wp_postmeta表上POST_ID:
LEFT JOIN {$wpdb->postmeta} AS postmeta ON order_items.order_id = postmeta.post_id
-
插入此2号线到您的WHERE:
AND postmeta.meta_key = '_customer_user' AND postmeta.meta_value = '$customer_id'
然后是客户循环内的循环,通过得到的总和客户
这是代码:
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>';
}
测试和工程。它将输出列表与Customer IDs
和相应的Total PR
。
参考文献:
嗨,感谢您的回答,但它不显示结果伴侣。 –
@PatrickJosephAragones年后,**它的工作原理!!! **但这已经太晚了:) ...我只是想找到解决方案,现在完成了。答案是在'wp_postmeta'表中查找客户ID,而不是在'wp_posts'表中查找。回头见… – LoicTheAztec
究竟你的意思是用 “不承认说用户”?你有错误吗?意外的输出?还有别的吗?请[编辑]问题以包含尽可能多的信息! – Carpetsmoker