I am having trouble displaying all completed orders. I have this site that uses WPLMS theme.
I need to show the custom reporting but I am unable to understand the database. I would like to have this info:
Name of Course purchased
Name of person who purchased it.
Voucher code used to buy it
Date of purchase
Amount paid
This query is working fine on one website that uses Academy theme but on WPLMS it returns null
select (select `pm4`.`meta_value` from `wp_postmeta` `pm4` where
((`p`.`ID` = `pm4`.`post_id`) and (`pm4`.`meta_key` = '_customer_user')))
AS `student_id`,(select `pm3`.`meta_value` from `wp_postmeta` `pm3`
where ((`p`.`ID` = `pm3`.`post_id`) and (`pm3`.`meta_key` = '_order_total')))
AS `order_total_sum`,(select `wpoi1`.`order_item_name` from
`wp_woocommerce_order_items` `wpoi1` where ((`wpoi1`.`order_id` = `p`.`ID`)
and (`wpoi1`.`order_item_type` = 'coupon')) limit 1)
AS `coupon_used`,(select `wpoi2`.`order_item_name` from
`wp_woocommerce_order_items` `wpoi2` where ((`wpoi2`.`order_id` = `p`.`ID`)
and (`wpoi2`.`order_item_type` = 'line_item')) limit 1) AS
`product_name`,`p`.`post_date` AS `order_date`,`p`.`ID` AS `ID` from
(`wp_posts` `p` join `wp_term_relationships` `wptr`) where
((`p`.`post_type` = 'shop_order') and (`wptr`.`object_id` = `p`.`ID`)
and `wptr`.`term_taxonomy_id` in (select `wp_terms`.`term_id` from
`wp_terms` where (`wp_terms`.`slug` = 'completed')));
This returns no rows but if I remove the below code from the end
AND (wptr.object_id = p.ID)
and wptr.term_taxonomy_id in (select wp_terms.term_id from
wp_terms where (wp_terms.slug = ‘completed’))
I receive all the rows with so many empty columns in it. This is working perfectly on one website. Let me know what is the issue. Also in my database there no slug named “completed”
Thanks
