mysql - 根据动态日期范围查询帖子元值

Question

我希望通过产品 SQL 查询修改默认的 woocommerce 订阅，以仅反映在接下来的 7 天内具有元键 = _schedule_next_payment 和元值的订阅的订阅产品计数。这样做的原因是我每周交付产品，并试图预测下周可能订购的所有产品。

我添加了以下查询段

LEFT JOIN (
                SELECT *
                FROM {$wpdb->posts} as subscriptionjk
                INNER JOIN {$wpdb->postmeta} AS postmeta
                    ON subscriptionjk.id = postmeta.post_id
                WHERE postmeta.meta_key = '_schedule_next_payment'
                    AND postmeta.meta_value BETWEEN '2020-09-07 00:00:00' AND '2020-09-14 00:00:00'

在 class-wcs-report-subscriptions-by-product 的默认 woocommerce 查询中，该查询生成按产品分类的订阅报告

"SELECT product.id as product_id,
                product.post_parent as parent_product_id,
                product.post_title as product_name,
                mo.product_type,
                COUNT(subscription_line_items.subscription_id) as subscription_count,
                SUM(subscription_line_items.product_total) as recurring_total
            FROM {$wpdb->posts} AS product
            LEFT JOIN (
                SELECT tr.object_id AS product_id, t.slug AS product_type
                FROM {$wpdb->prefix}term_relationships AS tr
                INNER JOIN {$wpdb->prefix}term_taxonomy AS x
                    ON ( x.taxonomy = 'product_type' AND x.term_taxonomy_id = tr.term_taxonomy_id )
                INNER JOIN {$wpdb->prefix}terms AS t
                    ON t.term_id = x.term_id
            ) AS mo
                ON product.id = mo.product_id
            LEFT JOIN (
                SELECT wcoitems.order_id as subscription_id, wcoimeta.meta_value as product_id, wcoimeta.order_item_id, wcoimeta2.meta_value as product_total
                FROM {$wpdb->prefix}woocommerce_order_items AS wcoitems
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS wcoimeta
                    ON wcoimeta.order_item_id = wcoitems.order_item_id
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS wcoimeta2
                    ON wcoimeta2.order_item_id = wcoitems.order_item_id
                WHERE wcoitems.order_item_type = 'line_item'
                    AND ( wcoimeta.meta_key = '_product_id' OR wcoimeta.meta_key = '_variation_id' )
                    AND wcoimeta2.meta_key = '_line_total'
            ) as subscription_line_items
                ON product.id = subscription_line_items.product_id
            LEFT JOIN (
                SELECT *
                FROM {$wpdb->posts} as subscriptionjk
                INNER JOIN {$wpdb->postmeta} AS postmeta
                    ON subscriptionjk.id = postmeta.post_id
                WHERE postmeta.meta_key = '_schedule_next_payment'
                    AND postmeta.meta_value BETWEEN '2020-09-07 00:00:00' AND '2020-09-14 00:00:00'
            ) as subscriptions
                ON subscriptions.ID = subscription_line_items.subscription_id
            WHERE product.post_status = 'publish'
                 AND ( product.post_type = 'product' OR product.post_type = 'product_variation' )
                 AND subscriptions.post_type = 'shop_subscription'
                 AND subscriptions.post_status NOT IN( 'wc-pending', 'trash' )
            GROUP BY product.id
            ORDER BY COUNT(subscription_line_items.subscription_id) DESC" );

使用该功能，它可以正常工作并提供我正在寻找的结果。但是，我尝试使用此堆栈溢出问题的指导将那些硬编码的日期替换为 BETWEEN 函数，并且我尝试使用不同的配置它没有提供任何结果。

我试过这个：

AND postmeta.meta_value BETWEEN GETDATE() AND DATEADD(day, -7, GETDATE())

标签： mysqlwordpresswoocommercemetadata

Answer 1

这是有效的解决方案：

LEFT JOIN (
                SELECT *
                FROM {$wpdb->posts} as subscriptionjk
                INNER JOIN {$wpdb->postmeta} AS postmeta
                    ON subscriptionjk.id = postmeta.post_id
                WHERE postmeta.meta_key = '_schedule_next_payment'
                    AND postmeta.meta_value BETWEEN CURDATE() AND (CURDATE() + INTERVAL 7 DAY)
            ) as subscriptions
                ON subscriptions.ID = subscription_line_items.subscription_id