Exclude or Include category ids in WP_Query

Question

I have a wordpress setup which has more than 300 categories.

Now I have requirement to give some flexibility to choose the categories. In that case I initially pre-ticked all the categories, if someone need to exclude a category they can deselect it.

Now the problem I am facing is how to give accurate results according the category selection.

My first approach was just exclude all the deselect categories as bellow,

eg: exclude 10,11,12 categories

$args = array(
    'category__not_in' => array('10','11','12')
);

Let's say I have a post which was ticked under category 12 & 13. From above code I will not get that post as a result as it is excluding posts under the category 12. But ideally it should be in the results as category 13 was not deselected.

As I solution I could use 'category__in' option with all selected category ids. But my worry is the list would be very long even-though it is coming programmatically, I am not sure about the wp_query overhead as I have more than 300 categories.

Anyone has a better idea how to solve this issue.

Answer 1

As you probably know it, categories are taxonomies. When you use the arguments such as category__in, it will add a tax query to your WP_Query(). So, your situation would be something like this:

$args = array(
    'post_type' => 'post',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 12 ),
            'operator' => 'IN',
        ),
        array(
            'taxonomy' => 'category',
            'field'    => 'term_id',
            'terms'    => array( 11, 12, 13 ),
            'operator' => 'NOT IN',
        ),
    ),
);
$query = new WP_Query( $args );

I wouldn't think of performance issues here. This is most likely your only solution, if you don't want to directly query the posts from database by using a SQL query ( This might improve the performance a bit ).

Answer 2

Let's assume that we have 4 posts and 4 categories.

+----+--------+
| ID |  Post  |
+----+--------+
|  1 | Test 1 |
|  2 | Test 2 |
|  3 | Test 3 |
|  4 | Test 4 |
+----+--------+

+----+------------+
| ID |  Category  |
+----+------------+
|  1 | Category 1 |
|  2 | Category 2 |
|  3 | Category 3 |
|  4 | Category 4 |
+----+------------+

+--------+------------------------+
|  Post  |        Category        |
+--------+------------------------+
| Test 1 | Category 1, Category 2 |
| Test 2 | Category 2             |
| Test 3 | Category 3             |
| Test 4 | Category 4             |
+--------+------------------------+

If I understood your question correctly, you want to get Test 1 post using category__not_in parameter. Arguments to your query will look's like:

$args = array(
    'category__not_in' => array(2, 3, 4)
);

The problem with category__not_in is that it produce NOT IN SELECT SQL query.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
  AND (wp_posts.ID NOT IN
         ( SELECT object_id
          FROM wp_term_relationships
          WHERE term_taxonomy_id IN (2, 3, 4) ))
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

NOT IN SELECT will exclude all posts including Test 1. If only this SQL would use JOIN instead of NOT IN SELECT this will work.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
  AND (wp_term_relationships.term_taxonomy_id NOT IN (2, 3, 4))
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Above SQL will return only Test 1 post. We can make a little trick to produce such a query using WP_Query class. Instead of using category__not_in parameter replace it with category__in parameter and add post_where filter which will modify SQL directly to our purpose.

function wp_286618_get_posts() {

    $query = new WP_Query( array(
        'post_type' => 'post',
        'category__in' => array( 2, 3, 4 ) // Use `category__in` to force JOIN SQL query.
    ) );

    return $query->get_posts();
}

function wp_286618_replace_in_operator($where, $object) {

    $search = 'term_taxonomy_id IN'; // Search IN operator created by `category__in` parameter.
    $replace = 'term_taxonomy_id NOT IN'; // Replace IN operator to NOT IN

    $where = str_replace($search, $replace, $where);

    return $where;
}

add_filter( 'posts_where', 'wp_286618_replace_in_operator', 10, 2 ); // Add filter to replace IN operator

$posts = wp_286618_get_posts(); // Will return only Test 1 post

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

The advantage of this solution over others is that I don't need to know other categories ID, and it will keep your post loop clean.

Answer 3

Why are you pre-ticking all the categories? Isn't it easier to display all the results and at the same time have the categories all un-ticked? Then when the user selects a few (who's going to select 300 categories?) you can run a query with category__in.

Answer 4

Am not sure but I think you cannot do this using the default behavior/options of WP_Query. So maybe a work around will be to implement a function that will do this test for you after selecting all the posts.

Of course, the drawback of this method is that you have to first select all the posts then filter them, but it can be a solution for you problem. So you can do somthing like this :

<?php 

function test_categorie($cats,$ex_cats) {

    $test = false; //we consider that this post is excluded until we found a category that doesn't belong to the array
    foreach ($cats as $cat){
        if(!in_array(intval($cat->term_id),$ex_cats)) {
            $test = true;
            //We can exit the loop as this post have at least one category not excluded so we can consider it
            break;
        }

    }
    return $test;
}
//Define the excluded categorie here
$exclude_cat = array(11,12,13);

$args = array(
    'posts_per_page'   => -1,
    'post_type'        => 'post',
);

$the_query = new WP_Query($args );
if ( $the_query->have_posts() ) :
     while ( $the_query->have_posts() ) : $the_query->the_post(); 
            //we do our test, if(false) we don't consider the post and we continue to the next
            if(!test_categorie(get_the_category(),$exclude_cat))
                continue;

            /*  
                Add you code here
            */

    endwhile;
    wp_reset_postdata(); 
endif;

?>

Answer 5

try this one. I guess this is a bit dirty , but it works for me good!

    $skills = get_user_meta($curr_id , 'designer_skills');
    $skills = array_map('intval', explode(',' , $skills[0]));
    $args = array(
            'numberposts' => -1,
            'post_type' => 'project',
            'meta_query'    => array(
                'relation'      => 'AND',
                array(
                    'key'       => 'status',
                    'compare'   => '=',
                    'value'     => 'open'
                ),
                array(
                   'key'        => 'payment_status',
                    'compare'   => '=',
                    'value'     => true
               )
            )
        );
        $posts = get_posts( $args );
        if ($posts) {
            $count = 0;
            foreach ($posts as $project) {
                if (in_array(get_the_terms( $project->ID, 'projects')[0] -> term_id , $skills)){
                    //implement your own code here
                    }
            }
    }