Select post without category in wordpress

php, sql, wordpress

Solution

You can use a `where id is null` clause to demand that the `left join` hasn't succeeded:

SELECT  p.* 
FROM    wp_posts p
LEFT JOIN 
        wp_term_relationships rel
ON      p.ID = rel.object_id 
LEFT JOIN 
        wp_term_taxonomy tax
ON      tax.term_taxonomy_id = rel.term_taxonomy_id 
        AND tax.taxonomy = 'category' 
LEFT JOIN 
        wp_terms term
ON      term.term_id = tax.term_id
WHERE   p.post_type = 'post' 
        AND p.post_status = 'publish' 
        AND term.term_id is null -- No category found

A `not exist` clause can be used the accomplish the same:

SELECT  * 
FROM    wp_posts p
WHERE   p.post_type = 'post' 
        AND p.post_status = 'publish' 
        AND NOT EXISTS
        (
        SELECT  *
        FROM    wp_term_relationships rel
        JOIN    wp_term_taxonomy tax
        ON      tax.term_taxonomy_id = rel.term_taxonomy_id 
                AND tax.taxonomy = 'category' 
        JOIN    wp_terms term
        ON      term.term_id = tax.term_id
        WHERE   p.ID = rel.object_id 
        )

Problem

I have this script that select a list from a specific category: ``` SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON wp_posts.ID=wp_term_relationships.object_id LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id LEFT JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' AND ( wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id = wp_terms.term_id AND wp_terms.name = '$category' ) ``` How do I select posts that are not categorized? I tried `$category = ''` and got 0 rows because `wp_terms.name =''` does not have a field.

Original source