sql - Wordpress WooCommerce SQL 查询以通过账单电子邮件和名称获取订单详细信息
问题描述
我找到了一个帖子查询和订单的 postmeta 并尝试运行它并且它有效。但它只有在没有 where 子句的情况下才有效(我得到 1 行包含订单详细信息,1 行因为只有一个订单)
但是,当我添加 Where 子句以通过 billing_email 进行检查时,出现错误这是查询
select
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
WHERE
billing_email = 'email@email.com'
group by
p.ID
我得到的错误如下:
1054 - 'where 子句'中的未知列'billing_email'
另一个有趣的事情是当我在 where 子句中使用它时
pm.meta_value = 'email@email.com'
我得到一个结果,但只显示 Order_id 、 post_date 和 billing_email ,其余为 NULL
解决方案
推荐阅读
- flutter - how to get only day number from date in flutter
- amazon-web-services - AWS Athena - 从 Lambda 连接
- spring - @RequestBody 在集成测试范围内仅包含带有 kotlin 数据类的空值
- python - 如何更新绘图并保留 UI 设置?
- react-native - React Native:博览会开始导致抛出错误:找不到模块
- python - 不能@不和谐的人使用请求
- java - 变量 nonSmoking 始终为空。如何将其设置为 1 或 0?
- javascript - 在 reactjs 中发出 POST 请求时,数据未插入有效负载中
- java - 将 JFormattedTextField.getText() 返回结果转换为自定义类型
- r - 如何从数据表中的列表元素中减去数字元素