php - 在wordpress中选择没有类别的帖子

Question

我有这个脚本可以从特定类别中选择一个列表:

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

如何选择未分类的帖子？我尝试了 $category = '' 并得到了 0 行，因为 wp_terms.name ='' 没有字段。

Answer 1

您可以使用 where id is null 子句来要求 left join 没有成功:

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

一个 not exist 子句可以用来完成同样的事情:

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 
        )