0

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?)

  • What do you mean a standard field query? And yes, categories are not fields, they are taxonomies. – Max Yudin Dec 8 '17 at 12:45
  • @Max Yudin - Well, i was trying to say that i wanted to combining a query on the meta table (which is non-standard insofar as it is optional and not created by the WP system as standard) with a query on the 'standard', or generic table. I think the rest of the explanation should explain it. Thanks for any insight from your side. – Donquick Dec 11 '17 at 4:08
0

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.

  • That's great, thank you. Not only have you answered the question, but i have learned something about customising the SQL manually. I will try your solution in the next day or two, thanks again. – Donquick Dec 11 '17 at 3:51
  • OK - I got there after a bit of frustration. My database references (not sure if this is the right term - i'm sure somebody will tell me) are 'wp_vodz_posts', 'wp_vodz_postmeta', etc. and not just 'wp_posts', 'wp_postmeta' etc. (i have no idea why) so that was starring me in the face for a while; also I had to change the meta value from '1' to 'true'. One thing that is driving my crazy is your use of %d in the code. Please can you explain how this become the category number in the SQL? Thx. – Donquick Dec 12 '17 at 7:34
  • wp_vodz_ is just a table prefix my is wp_ your is wp_vodz_ don't worry about that, just replace all my prefixes. Boolean values in MySQL are stored as 1 and 0 so I would rather use 1 and 0 instead of true false. Your true and false will be treated as strings. When you want to pass some values to SQL you have to use $wpdb->prepare method which will prepare your SQL to prevent SQL injection attacks. First argument of this method is SQL string and rest are are arguments which you want to pass to SQL. %d mean I want to pass digit as argument. bit.ly/2nSi702 – kierzniak Dec 12 '17 at 8:46
  • Thanks - nice link: that article is very revealing for future situations. Cheers. – Donquick Dec 13 '17 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.