使用 SQL 查询获取所有 WooCommerce 客户支付的订单 [英] Get all WooCommerce customers paid orders with a SQL query
问题描述
我需要一个查询,返回所有订单已批准付款的用户.
I need a query that returns me all the users with orders with payment approved.
我很难找到它在数据库中的存储位置.我只找到了 post_status 'wc-complete',但我认为这不是正确的信息.
I'm having a hard time finding where in the db this is stored. I found only post_status 'wc-complete', but I don't think this is the right info.
SELECT a.post_status, b.meta_value FROM wp_posts a, wp_postmeta b
WHERE a.ID = b.post_id
AND a.post_type = 'shop_order'
AND a.post_status = 'wc-completed'
推荐答案
对已付款订单使用已完成"订单状态是正确的
Using "completed" order status for paid orders is correct
这是一个带有 SQL 查询的自定义函数,它将输出一个格式化的用户 ID 数组及其已完成的订单(已支付/已接受):
Here is a custom function with a SQL query, that will output an formatted array of User IDs with their completed orders (paid / accepted):
function completed_orders_ids_by_costumer_id(){
global $wpdb;
$query = $wpdb->get_results("
SELECT pm.meta_value AS user_id, pm.post_id AS order_id
FROM {$wpdb->prefix}postmeta AS pm
LEFT JOIN {$wpdb->prefix}posts AS p
ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order'
AND p.post_status = 'wc-completed'
AND pm.meta_key = '_customer_user'
ORDER BY pm.meta_value ASC, pm.post_id DESC
");
// We format the array by user ID
foreach($query as $result)
$results[$result->user_id][] = $result->order_id;
return $results;
}
代码位于活动子主题(或主题)的 function.php 文件或任何插件文件中.
经过测试并有效.
示例用法 仅用于测试以查看原始输出数据
echo '<pre>'; print_r(completed_orders_ids_by_costumer_id()); echo '</pre>';
You will get something like:
你会得到类似的东西:
<小时><块引用>
现在在 wp_postmeta
表中,您还将有一些其他与付费订单相关的元键,您可以使用:
• '_paid_date'
• '_date_paid'
• '_date_completed'
• '_completed_date'
Now in
wp_postmeta
table you will also some other meta keys related to paid orders that you could use:
•'_paid_date'
•'_date_paid'
•'_date_completed'
•'_completed_date'
但是定位已完成"的订单状态就可以了
But targeting "completed" order status is just fine
使用其他人列出的元键,您可以进行 SQL 查询(可以替换我们在函数中的第一个查询):
Using that others listed meta keys, you can make your SQL query (that can replace our first query in the function):
$query = $wpdb->get_results("
SELECT DISTINCT pm.meta_value AS user_id, pm.post_id AS order_id
FROM {$wpdb->prefix}postmeta AS pm
LEFT JOIN {$wpdb->prefix}posts AS p
ON pm.post_id = p.ID
LEFT JOIN {$wpdb->prefix}postmeta AS pm2
ON p.ID = pm2.post_id
WHERE p.post_type = 'shop_order'
AND pm.meta_key = '_customer_user'
AND pm2.meta_key IN ( '_paid_date', '_paid_date', '_date_completed', '_completed_date' )
AND pm2.meta_value != ''
ORDER BY pm.meta_value ASC, pm.post_id DESC
");
经过测试并且也能正常工作......
Tested and works too…
这篇关于使用 SQL 查询获取所有 WooCommerce 客户支付的订单的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!