I have created a plugin where employers are able to search freelancers and hire them. Freelancer can select dates from a calendar to mark those dates as available to work.

Employers search between dates (from and to) to see the available freelancers then they can shortlist or send job brief to them.

The problem is when employer select two dates which has many days gap, the query just goes on time out.

I have tried adding availability dates into user_meta as an array then queried as following but its obvious that gap of many days will have time out problem as I was using LIKE.

$date_query = array();

if ( ! empty( $from ) && ! empty( $to ) ) {

  $start = new DateTime( $from );
  $end = new DateTime( $to );
  $end = $end->modify( '+1 day' ); // Include the end date in the period
  $interval = new DateInterval( 'P1D' ); // 1 Day interval
  $date_range = new DatePeriod( $start, $interval ,$end );
  $date_query = [ 'relation' => 'AND' ];

  foreach( $date_range as $date ) {

    $date_query[] = array(
      'key' => 'fmaa_available_dates',
      'value' => $date->format( 'Y-m-d' ),
      'compare' => 'LIKE'
    );

  }

}

I also tried to add them into separate user meta using add_user_meta() but that also caused the same problem as the query goes too long for having a lot of days.

if ( ! empty( $from ) && ! empty( $to ) ) {

  $start = new DateTime( $from );
  $end = new DateTime( $to );
  $end = $end->modify( '+1 day' ); // Include the end date in the period
  $interval = new DateInterval( 'P1D' ); // 1 Day interval
  $date_range = new DatePeriod( $start, $interval ,$end );
  $date_query = [ 'relation' => 'AND' ];

  foreach ( $date_range as $date ) {

    $date_query[] = array(
      'key' => 'fmaa_availability_dates',
      'value' => $date->format( 'Y-m-d' ),
      'compare' => 'IN'
    );

  }

}