Is it possible to customize meta query in this way?

Question

I have a custom post type called promotions.

In each promotions' post meta, I store two fields, promo_start_date and promo_end_date.

The idea is to only show promotions on the frontend, where the

current_date >= promo_start_date AND current_date <= promo_end_date

For example, if the promotion has the value of 2022-12-18 in the promo_start_date and 2023-01-08 in the promo_end_date, it will only show up on the frontend on the 2022-12-18 and then display till the 2023-01-08.

I was wondering how possible it is to achieve this with meta_query.

$args = [
    'paged' => 1
    'post_type' => 'promotions',
    'posts_per_page' => 15,
    'meta_query' => [
        [
            'key' => 'promo_start_date',
            'value' => gmdate('Y-m-d'),
            'type' => 'DATE',
            'compare' => '>=',
        ],
        [
            'key' => 'promo_end_date',
            'value' => gmdate('Y-m-d'),
            'type' => 'DATE',
            'compare' => '<=',
        ],
    ],
];

I can easily implement this in raw sql but unfortunately I am tweaking a filter for a plugin in order to achieve the result that I need.

The filter is coming from the Search and Filter plugin and its documentation can be found here

And here is an example of the code that I am trying to implement

function me_sf_edit_query_args($query_args, $sfid) {
    // if search form has specified ID, change query args
    if ($sfid == 11000) {
        $query_args['meta_query'] = [
            [
                'key' => 'promo_start_date',
                'value' => gmdate('Y-m-d'),
                'type' => 'DATE',
                'compare' => '>=',
            ],
            [
                'key' => 'promo_end_date',
                'value' => gmdate('Y-m-d'),
                'type' => 'DATE',
                'compare' => '<=',
            ],
        ];
    }

    return $query_args;
}
add_filter('sf_edit_query_args', 'me_sf_edit_query_args', 20, 2);

Thank you

Answer 1

$today = current_time( 'Y-m-d' );
$args = [
    'paged' => 1,
    'post_type' => 'promotions',
    'posts_per_page' => 15,
    'meta_key'     => 'promo_start_date',
    'meta_value'   => $today,
    'meta_compare' => '>=',
    'meta_query' => [
        [
            'key' => 'promo_end_date',
            'value' => $today,
            'compare' => '<=',
        ],
    ],
];