Combining a meta_query with a category filter

Question

In each category there is one special post with a meta key called 'overview' ('overview' = 'true'). When a category is selected we get all posts in the category, including the overview item and all post in the child categories including their 'overview' items. I want to use pre_get_posts to filter out the 'overview' items for all the child categories, but keep the overview of the actual category i have selected.

I can set a meta_query filter (with one or more conditions), but how do i include a standard field query at the same time. [Categories are a bit strange anyway as they are not a simple field, if i understand it correctly].

This is part of a larger function which is in the pre_get_posts, add_action.

<?php
if ($query->is_category()) {
    // remove overview items for categories other than the one selected
    $queried_object = get_queried_object();
    $this_cat = $queried_object->term_id;

    $meta_query['excl_child'] = array(
        'relation' => 'OR',
        // the first of the two arrays that follow is where I need some help
        array(
            'key'     => 'cat',
            'compare' => '=',
            'value'   => $this_cat
        ),
        array(
            'key'     => 'overview',
            'compare' => '!=',
            'value'   => 'true'
        );

    $query->set('meta_query',$meta_query);
}

The logic is that if the post is either NOT an overview OR is in the actual category selected then it should be included.

(I thought about using post__in to add the specific post by number after specifying a meta_query. Is this an option and what would the code to find the post_ID for that be, please?)

Answer 1

As you mention you need something like mixed tax and meta query with possibility to join them by condition OR or AND, which does not exist in WordPress. You can modify SQL directly using filters to get your result.

/**
 * This function will add meta_key to WordPress query.
 * It will take care of adding JOIN and WHERE clause to SQL.
 */
function wpse_288118_add_overview_meta_key( $query ) {

    if ( ! is_admin() && $query->is_main_query() && $query->is_category() ) {

        $query->set( 'meta_key', 'overview' );

        // Only modify SQL when we set the overview key
        add_filter('posts_where', 'wpse_288118_add_category_and_overview_condition', 10, 2);
    }

    return $query;
}

/**
 * This function will add custom SQL to request SQL
 */
function wpse_288118_add_category_and_overview_condition( $where, $query ) {

    global $wpdb;

    // Current category id
    $category = $query->get( 'cat' );

    // This is most important line. We do not want posts which has "overview" set to 1
    // and category different than our current category.
    $sql = 'AND !(wp_postmeta.meta_value = 1 && wp_term_relationships.term_taxonomy_id != %d)';
    $sql = $wpdb->prepare( $sql, $category );

    // Add our custom SQL
    $where .= $sql;

    // Remove filter to not modify other queries
    remove_filter('posts_where', 'wpse_288118_add_category_and_overview_condition', 10, 2);

    return $where;

}

/**
 * Add filter to modify query
 */
add_filter( 'pre_get_posts', 'wpse_288118_add_overview_meta_key' );

This code will produce something like that SQL:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1
  AND (wp_term_relationships.term_taxonomy_id IN (2, 3))
  AND (wp_postmeta.meta_key = 'overview')
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish')
  AND !(wp_postmeta.meta_value = 1 && wp_term_relationships.term_taxonomy_id != 2)
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Where 2 is my main category and 3 is child category of 2.

Please remember that this solution will only works if all posts which you want to get has overview key set to something 0 or 1.