Combining a meta_query with a category filter

Question

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.

Related Posts: