6

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.

0
9
+25

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

4
  • 1
    But don't you think we will always have the same issue ? the OP wanted to use something to avoid listing all the categories, but in your case you will be obliged to list the categories inside the IN part, right ? Nov 26 '17 at 18:40
  • yes, it is again leads to the category__in option Nov 28 '17 at 13:44
  • category__in or category__and is what you need here like Jack says. If you're looking to speed up your queries, perhaps consider using transients. Nov 29 '17 at 5:45
  • Also, graziondev's response below is also correct, there is no need to use category__in when first making your query since you're including all categories by default. Load that query into your transient and then filter subsequent requests from that data if you want speed. Nov 29 '17 at 5:47
6

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.

0
2

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.

1
  • not a option for me though. normally people tend to select almost all the categories, but some of them were doesn't like to see unnecessary posts, so in that case they might deselect the categories. But in my guess more than 90% categories would be in the selection. That is the reason behind we are giving the deselecting rather than selecting. Nov 23 '17 at 5:15
1

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;

?>
1
  • This answer will work, I think, although you may run into some problems if the use or output depends on the query being sound in relation to exclusion - for instance for accurate pagination: You might get some pages meant to show, say, 5 items, but instead showing 2, for example. There are workarounds for that, too, of course, though there will always be trade-offs of one type or another
    – CK MacLeod
    Nov 29 '17 at 7:55
1

I think I'd tackle performance issue(s) if and when they crop up but if you're really concerned turn on MySQL's slow query log and use microtime to track how long this particular query / hydration takes. To answer your question a simple solution would be to use array_diff. For example:

$notIn = [10, 11, 12];
$in = [11];

$args = array(
    'category__not_in' => array_diff($notIn, $in)
);
1
  • If you're going to downvote please take the time to share why. Thank you.
    – JSP
    Nov 29 '17 at 15:16
1

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
                    }
            }
    }

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.