php - 尝试让订单总数显示在 Woocommerce 的自定义报告中

Question

我正在开发一个插件，它将一些自定义报告添加到 Wordpress 中的 Woocommerce 报告部分。我们的结帐页面有两个自定义字段，用户必须在结帐之前回答这些字段。结帐时的这些自定义字段类似于“您是如何找到我们的？”有几个选项，其中一个选项是“购买原因？”有几个选项。

该插件将显示按日期范围指定的每个选项的使用次数。我所有这些工作都没有问题。现在我想在报告中添加每个选项产生的收入。

这将显示元值(购买原因)及其使用次数:

$sql = "SELECT *,

                count(distinct order_id) AS 'sale_total'


                FROM {$wpdb->prefix}woocommerce_order_items AS order_items

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

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

                LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
                LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
                LEFT JOIN {$wpdb->terms} AS term USING( term_id )

                WHERE posts.post_type             = 'shop_order' 
                AND   posts.post_status           = 'publish'

                AND   reason.meta_key            = 'reason_for_purchase'
                AND   term.slug IN ('completed','canceled','failed','processing')
                AND   post_date > '" . date('Y-m-d', $start_date ) . "'
                AND   post_date < '" . date('Y-m-d', strtotime('+1 day', $end_date ) ) . "'
                GROUP BY reason.meta_value";

但是当我尝试向其中添加 _order_totals 时，我可以获得总计和每个使用的次数，但元值(购买原因)不再显示？这是我到目前为止所拥有的:

$sql = "SELECT SUM( order_item_meta.meta_value ) AS order_sum,

                count(distinct order_id) AS 'sale_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->postmeta} AS reason
                    ON order_items.order_id = reason.post_id

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

                LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
                LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
                LEFT JOIN {$wpdb->terms} AS term USING( term_id )

                WHERE posts.post_type             = 'shop_order' 
                AND   posts.post_status           = 'publish'
                AND   order_items.order_item_type = 'line_item'
                AND   order_item_meta.meta_key    = '_line_total'

                AND   reason.meta_key            = 'reason_for_purchase'
                AND   term.slug IN ('completed','canceled','failed','processing')
                AND   post_date > '" . date('Y-m-d', $start_date ) . "'
                AND   post_date < '" . date('Y-m-d', strtotime('+1 day', $end_date ) ) . "'
                GROUP BY reason.meta_value";

我想做的就是显示元值、它们被使用的次数以及每次产生的收入。它几乎可以工作，我可以让 3 个中的 2 个工作，但无法让所有 3 个工作，我觉得我的问题来自 SUM( order_item_meta.meta_value ) AS order_sum 但我不确定

Answer 1

明白了，对于其他正在寻找的人来说 -

$sql = "SELECT *,
                SUM( order_item_meta.meta_value ) AS order_sum,
                count(distinct order_id) AS 'sale_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->postmeta} AS reason
                    ON order_items.order_id = reason.post_id

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

                LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID = rel.object_ID
                LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
                LEFT JOIN {$wpdb->terms} AS term USING( term_id )

                WHERE posts.post_type             = 'shop_order' 
                AND   posts.post_status           = 'publish'
                AND   order_items.order_item_type = 'line_item'
                AND   order_item_meta.meta_key    = '_line_total'

                AND   reason.meta_key            = 'reason_for_purchase'
                AND   term.slug IN ('completed','canceled','failed','processing')
                AND   post_date > '" . date('Y-m-d', $start_date ) . "'
                AND   post_date < '" . date('Y-m-d', strtotime('+1 day', $end_date ) ) . "'
                GROUP BY reason.meta_value";

然后为了显示它，我做了:

 <?php

        foreach($result as $value) {
            $order_total_sum = $value['order_sum'];
            $rounded_total = round($order_total_sum,2);
             ?>
            <tr>
                <td><?php echo $value['meta_value']; ?></td>
                <td><?php echo $value['sale_total']; ?></td>
                <td><?php echo get_woocommerce_currency_symbol().$rounded_total; ?></td>
            </tr>
    <?php

        }
    ?>