meta_query logic with query

Question

I’m currently building a pre_get_posts function that will do the following:

Filter out from the filters that I have set up but also I need some date filtering logic, for example if I have a project that is
from the 1st to the 15th as an example:
If

  1. Filter = from 2nd to 15th = Project shows up

  2. Filter = from 1st to 12th = Project shows up

  3. Filter = from 3rd to 10th = Project shows up

  4. Filter = from 1st to the 15th = Project shows up

Basically if the start date is between the project date or if the end date is between the project dates

This is the query that i have written so far:

$meta_query = array(
        'relation' => 'AND',
        array(
            array(
                'key' => 'place',
                'value' => $place,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'industry',
                'value' => $industry,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'type',
                'value' => $type,
                'compare' => 'LIKE',
            ),

        ),
        array(
            'relation' => 'OR',
            array(
                'key' => 'date_start',
                'type' => 'DATE',
                'value' => $date_start_formatted,
                'compare' => '=',
            ),
            array(
                'key' => 'date_end',
                'type' => 'DATE',
                'value' => $date_end_formatted,
                'compare' => '=',
            ),
        ),
        array(
            'relation' => 'OR',
            array(
                'key' => 'date_start',
                'type' => 'DATE',
                'value' => array($date_start_formatted, $date_end_formatted),
                'compare' => 'BETWEEN',
            ),
            array(
                'key' => 'date_end',
                'type' => 'DATE',
                'value' => array($date_end_formatted, $date_start_formatted),
                'compare' => 'BETWEEN',
            ),
        ),

but the logic seems to fail if i select the option 3 from the examples, the project does not show up.
Would greatly appreciate some help or directions: also tried this:

 $meta_query = array(
        'relation' => 'AND',
        array(
            array(
                'key' => 'place',
                'value' => $place,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'industry',
                'value' => $industry,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'type',
                'value' => $type,
                'compare' => 'LIKE',
            ),
        ),
        array(
            'relation' => 'OR',
            array(
                'key' => 'date_start',
                'type' => 'DATETIME',
                'value' => array($date_start_formatted, $date_end_formatted),
                'compare' => 'BETWEEN',
            ),
            array(
                'key' => 'date_end',
                'type' => 'DATETIME',
                'value' => array($date_start_formatted, $date_end_formatted),
                'compare' => 'BETWEEN',
            ),
        ),
     );

this code works fine for all scenario apart from this one:

  1. Filter = from 3rd to 10th = Project shows up

any help is greatly appreciated!

0
suiteguy 2 months 0 Answers 8 views 0

Leave an answer