Query Post Meta Value Based on dynamic date range

Question

I am looking to modify the default woocommerce Subscriptions by Product SQL query to only reflect the subscriptions product count for subscriptions that have a meta key = _schedule_next_payment and meta value within the next 7 days. The reason for this is I deliver product on a weekly basis and am trying to forecast all products that will likely be ordered in the next week.

I added the following query segment

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'

inside the default woocommerce query for class-wcs-report-subscriptions-by-product which generates the Subscriptions by Product report

"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" );

With that function as is it works and delivers the results I am looking for. However I have tried replacing those hard coded dates with a BETWEEN function using guidance from this stack overflow question and with different configurations I am trying it delivers no results.

I have tried this:

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

Incidentally, given that there's no aggregation in your subqueries, they don't really need to be subqueries, and you're returning an arbitrary ('indeterminate') value for mo.product_type. — Strawberry, Sep 07 '20 at 06:17
Hey @Strawberry, thanks for your reply. I am not sure how to apply what you're saying. Are your comments general feedback on the query, or advice / related to how to go about adding the desired filter for the meta key _schedule_next_payment? — Josh_Katz, Sep 07 '20 at 11:45
I updated the question to be more specific, and to reflect a new portion of the SQL query I wrote which has me part of the way there. — Josh_Katz, Sep 07 '20 at 13:35
A friend @Kirit Dholakiya helped me resolve this. Below is the solution — Josh_Katz, Sep 07 '20 at 14:30

score 0 · Answer 1 · answered Sep 07 '20 at 14:31

Here is the solution that worked:

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

Query Post Meta Value Based on dynamic date range

1 Answers1