Search between dates in a WP Query

Question

In the posts I have a date field and a start and end date range. I need to query if it matches date or if it matches between the dates in the range. This Query works, but how can I optimize?

$args = array(
 'post_type' => array('post'),
 'post_status' => array( 'publish' ),
 'posts_per_page' => 20,
 'paged' => 1,
 'meta_query' => array(
 'relation' => 'OR',
 array(
 'key' => 'this_day',
 'value' => $startday,
 'compare' => 'LIKE',
 ),
 array(
 'relation' => 'AND',
 array(
 'key' => 'date_start',
 'value' => $endday,
 'compare' => '<=',
 'type' => 'DATE',
 ),
 array(
 'key' => 'date_end',
 'value' => $startday,
 'compare' => '>=',
 'type' => 'DATE',
 ),
 )
 )
);

MySql sentence:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
WHERE 1=1
 AND
 (
 (
 (
 wp_postmeta.meta_key = 'this_day'
 AND wp_postmeta.meta_value = '20220701'
 )
 OR
 (
 (
 mt1.meta_key = 'date_start'
 AND CAST(mt1.meta_value AS DATE) <= '20220701' 
 )
 AND
 (
 mt2.meta_key = 'date_end'
 AND CAST(mt2.meta_value AS DATE) >= '20220701'
 )
 )
 )
 )
 AND wp_posts.post_type = 'post'
 AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

• J_H: Wow, big query! It would be interesting to see the EXPLAIN query plan. Assuming "small" date range in your query, we want a date index to be one of the initial steps in the plan.
• Toby Speight: It's worth following the advice of sql and presenting the definitions of your tables and their indexes.

Answer 1

Thank you for your answers :)

In the end I have solved it by performing a first query where I perform the first filtering and on that array, I perform the second query between the selected dates.

if( $_POST['date_ini']!='' && $_POST['date_end']!='' ){
 $date_ini = $_POST['date_ini']; // aaaa-mm-dd
 $date_end = $_POST['date_end'];
 $startday = str_replace('-','',$date_ini);
 $endday = str_replace('-','',$date_end);
 $date_result = date_i18n('j F Y', strtotime($startday));
} else {
 $startday = date('Ymd');
 $endday = date('Ymt', strtotime("+11 month")); // lastDayYear
 $date_result = date_i18n('j F Y', strtotime($startday)).' > '.date_i18n('j F Y', strtotime($endday));
}
$parameters = array();
for($i=$startday;$i<=$endday;$i = date("Ymd", strtotime($i ."+ 1 days"))){
 $parameters[] = array(
 'key' => 'alternate_days',
 'value' => '.*' . $i . '.*',
 'compare' => 'REGEXP',
 'type' => 'CHAR'
 );
}
$args_ = array(
 'post_type' => array('post'),
 'post_status' => array( 'publish' ),
 'cat' => 6, // if u need filter by cat
 'showposts' => -1,
 'meta_query' => array_merge( array(
 'relation' => 'OR',
 ),
 $parameters ) // parameters form filter
);
$args_2 = array(
 'post_type' => array('post'),
 'post_status' => array( 'publish' ),
 'cat' => 6, // if u need filter by cat
 'showposts' => -1,
 'meta_query' => array(
 'relation' => 'AND',
 array(
 'key' => 'date_ini',
 'value' => $endday,
 'compare' => '<=',
 'type' => 'DATE'
 ),
 array(
 'key' => 'date_end',
 'value' => $startday,
 'compare' => '>=',
 'type' => 'DATE'
 )
 )
);
$query = new \WP_Query( $args_ );
$query2 = new \WP_Query( $args_2 );
$filter_posts_by_id = array_column( array_merge( $query->posts , $query2->posts ), 'ID' );
$end_query = new WP_Query( array( 'post__in' => $filter_posts_by_id ) );

• Toby Speight: Thanks for posting this code. It's a good idea to summarise why you made the changes - a self-answer ought to review the code, just like any other answer.
• mickmackusa: $date_ini and $date_end are needless, single-use variables, right? @cRiSs

Answer 2

To my knowledge there aren't that many possibilities to optimize the query when you're doing a post meta search as all the values put into the post_metatable are stored as text.

On few occasions when I've had a similiar need to store and search dates I've created a custom db table with correctly typed columns for post ID, start and end datetimes. To manage the table I'd add new and update existing rows on save_post_{post_type} action, and delete rows on before_delete_post action.

On the query side with this setup there's at least two options how to handle the searching. Either query first the post ID's with the date range from the custom table and feed the found ID's to the WP_Query or modify the query with posts_where and posts_join filters.

• cRiSs rOcCa: I didn't really solve it that way, but it did help me to give it a spin and find a solution. Thankxxx