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?

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

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

$just_seven = new WP_Query(
    '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.

  • Accepted, however seems slower than using a raw query.... thanks anyway – Riccardo Oct 11 '10 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 '10 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 '10 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. – John P Bloch Oct 12 '10 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 '10 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.