1

I have built a query to select posts within a category. Works fine. But when I choose to add a secondary filter to exclude a category, the query return the same result set, as if the secondary category filter is being ignored.

In the following the query should select all posts in category 7 and exclude those in category 10:

$querystr = "SELECT * FROM $wpdb->posts 
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)  
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)  

WHERE ($wpdb->term_taxonomy.term_id = 7
   AND $wpdb->term_taxonomy.term_id <> 10  
   AND $wpdb->term_taxonomy.taxonomy = 'category'   
   AND $wpdb->posts.post_type = 'post'  
   AND $wpdb->posts.post_status = 'publish')";

Can someone help?

1
  • Any particular reason you need this done with raw SQL instead of using API functions?
    – Rarst
    Oct 11, 2010 at 15:32

1 Answer 1

1

I would use the built in API like Rarst mentioned. You could do something like this:

$just_seven = new WP_Query(
  array(
    'category__in' => array( 7 ),
    'category__not_in' => array( 10 )
  )
);

You would then have those items in $just_seven->posts.

However, if you MUST use a direct SQL statement, I'd suggest using INNER JOIN instead of LEFT JOIN.

6
  • Accepted, however seems slower than using a raw query.... thanks anyway
    – Riccardo
    Oct 11, 2010 at 16:48
  • I confirm that using WP_Query slows down the query, the pure SELECT statement is faster....Is there a way to speed up WP_query?
    – Riccardo
    Oct 12, 2010 at 9:14
  • What is really slowing down the pot is the IN approach. It is much faster to select posts belonging to ONE category and exclude the unwanted ones them later in the loop....
    – Riccardo
    Oct 12, 2010 at 9:59
  • It's not necessarily slower. If you use the normal WordPress APIs, you are able to leverage caching techniques that plugins may use to enhance the speed of these APIs, such that it's much faster than a direct database query. The other advantage of using WP_Query instead of a direct SQL query is that it automatically can create a WordPress loop, saving you time in setting all the global variables up, and enabling you to use the template tags. Oct 12, 2010 at 12:34
  • True. In my case I have designed the theme from the ground up so it wasn't a major concern. What definitely may discourage the SELECT approach is tables structure; this means more testing each time WP's major releases are rolled out.
    – Riccardo
    Oct 12, 2010 at 12: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.