[Plugin: WordPress Popular Posts] Category exclusion feature is broken; here’s the fix

The Category exclusion feature in this plugin is broken. The SQL query used to filter posts by the excluded categories is incorrect (line 554 of wordpress-popular-posts.php):

$exclude = " AND $wpdb->posts.ID NOT IN (SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id IN (".$instance['exclude-cats']['cats'].")) ";

The term_taxonomy_id is NOT the same as the category ID. The term_relationships table only references the term_taxonomy table, which is the table that actually includes the category ID (called term_id).

The real query needed to make this work is a bit hairy, but after a few hours of figuring out how WordPress categories are stored and testing and modifying code, I’ve come up with a working solution:

$exclude = " AND $wpdb->posts.ID NOT IN (

SELECT  object_id
FROM    $wpdb->term_relationships AS r
        JOIN $wpdb->term_taxonomy AS x ON x.term_taxonomy_id = r.term_taxonomy_id
        JOIN $wpdb->terms AS t ON t.term_id = x.term_id
WHERE   x.taxonomy = 'category'
        AND object_id IN
           (
            SELECT object_id
            FROM $wpdb->term_relationships AS r
            JOIN $wpdb->term_taxonomy AS x ON x.term_taxonomy_id = r.term_taxonomy_id
            JOIN $wpdb->terms AS t ON t.term_id = x.term_id
            WHERE   x.taxonomy = 'category'
            AND t.term_id IN  (".$instance['exclude-cats']['cats']."))) ";

This was not without the help of code from Damir Sudarevic on this StackOverflow post.

http://wordpress.org/extend/plugins/wordpress-popular-posts/