Complex custom_field search with meta_query

Question

Background

I am using Advanced Custom Fields Pro to manage my custom fields and they have a "repeater" field which contains sub fields that are stored as repeatername_X_fieldname where X is the row number of the repeater.

I have a custom post type student which has the repeater attendance which contains date and class.

So, when a student attends a class, it will store their attendance as follows

• meta_key:'attendance_X_date' meta_value:'20170701'
• meta_key:'attendance_X_class' meta_value:'History 101'

In order to search for any student who has been to a certain class, or attended within a certain date range, I have to hook onto get_meta_sql and convert my meta_query to use LIKE instead of = when the value contains %

function key_rewrite($parts){
    foreach($parts as &$part){
        $part = preg_replace("/(meta_key = )(\'[^']*[%][^']*\')/", "meta_key LIKE $2", $part);
    }
    return $parts;
}
add_action( 'get_meta_sql', 'key_rewrite');

This allows me to do something like

$args = array(
    'post_type' => 'student',
    'meta_query' => array(
        array(
            'key'=>'attendance_%_class',
            'compare'=>'=',
            'value'=>'History 101'
        )
    )
);
$my_query = new WP_Query($args);

in order to search for anyone who has attended History 101 OR

$args = array(
    'post_type' => 'student',
    'meta_query' => array(
        array(
            'key'=>'attendance_%_date',
            'compare'=>'>=',
            'value'=>'20170101'
        )
    )
);

To search for anyone who has attended this year.

Problem part 1

I need to be able to search for anyone who has attended 'History 101' this year.

Initially, it might seem that a simple AND on the meta_query would do the trick:

$args = array(
    'post_type' => 'student',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'=>'attendance_%_class',
            'compare'=>'=',
            'value'=>'History 101'
        ),
        array(
            'key'=>'attendance_%_date',
            'compare'=>'>=',
            'value'=>'20170101'
        )
    )
);

However, because the wildcards are not linked, this could actually return someone who attended 'History 101' last year, but a different class this year.

Problem part 2

I actually need to be able to get a list of everyone who has attended 'History 101' this year but has not shown up for the class at all in the past week. This complicates the problem further because I need to combine meta_query's EXISTS and NOT EXISTS with an additional condition. Again, on the surface, this sounds fairly simple using nested meta_queries:

$args = array(
    'post_type' => 'student',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'relation' => 'AND',
            array(
                //Just assume by some magic we resolved Problem part 1
                'key'=>'attendance_%_class',
                'compare'=>'=',
                'value'=>'History 101'
            ),
            array(
                'key'=>'attendance_%_date',
                'compare'=>'>=',
                'value'=>'20170101'
            )
        ),
        array(
            'relation' => 'AND',
            array(
                //again... magic!
                'key'=>'attendance_%_class',
                'compare'=>'=',
                'value'=>'History 101'
            ),
            array(
                'key'=>'attendance_%_date',
                'compare'=>'>',
                'value'=>'20170821'
            ),
            array(
                'key'=>'attendance_%_date',
                'compare'=>'NOT EXISTS'
            )
        )
    )
);

Obviously this is wrought with logical problems, but impressively WordPress solves most of them by joining in the postmeta table in once per use in the meta query. Unfortunately that means the > date portion is not used in the NOT EXISTS ON and thus can not use the IS NULL to test for it not existing.

I understand this was very complex and if you followed me, I am thoroughly impressed. If not, please ask questions so I can help clarify.

Yes, I am aware I could just completely write my own query, but I am trying to stick to the built in WordPress tools.

HELP!

Answer 1

I've been there... trying to put all in one complex meta query. At the end you need to manually process the generated SQL - moving brackets, replacing operators, quotes, etc.

At the end the query is so complex and have multiple JOINS to the postmeta table it becomes too expensive and slow.

I've chosen to achieve this by choosing slightly different approach. So, what you can do is divide the query to several sub-queries and combine them later with post__in and post__not_in,

For Example for Problem part 1:

/* Filter by class */
$history_students_ids = get_posts(array(
    'post_type'      => 'student',
    'fields'         => 'ids',
    'posts_per_page' => -1,
    'meta_query'     => array(
        array(
            'key'=>'attendance_%_class',
            'compare'=>'=',
            'value'=>'History 101'
        ),
    )
));

/* Filter by date */
$students = get_posts(array(
    'post_type' => 'student',
    'post__in' => $history_students_ids,
    'meta_query' => array(
        array(
            'key'=>'attendance_%_date',
            'compare'=>'>=',
            'value'=>'20170101'
        )
    )
));

The same goes for Problem part 2

$not_attended_history_students_ids = get_posts(array(
    'post_type' => 'student',
    'post__in' => $history_students_ids,
    'fields'         => 'ids',
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
            'key'=>'attendance_%_date',
            'compare'=>'NOT EXISTS'
        )
    )
));

$students = get_posts(array(
    'post_type' => 'student',
    'post__in' => $not_attended_history_students_ids,
    'meta_query' => array(
        array(
            'key'=>'attendance_%_date',
            'compare'=>'>=',
            'value'=>'20170101'
        )
    )
)); 

You can reverse attendance condition to be EXISTS and use post__not_in... I hope you have managed to understand the idea.