用于检索未指明类别的所有记录的SQL查询

时间：2017-02-19 作者：TrubinE

我需要获取未指定类别（分类法“类别”）的ID帖子。Post包括其他分类法。

我从代码中获得帖子：

$posts = $wpdb->get_results(\'SELECT ...\', , ARRAY_A);
我的查询选择所需的录制类型

SELECT distinct posts.ID FROM wp_posts AS posts 

LEFT JOIN wp_term_relationships AS cat_link ON
cat_link.object_id = posts.ID 

LEFT JOIN wp_term_taxonomy AS cat_tax ON
cat_link.term_taxonomy_id = cat_tax.term_taxonomy_id AND cat_tax.taxonomy NOT IN (\'category\') 

WHERE post_type = \'popular_music\' AND post_status = \'publish\' ORDER BY posts.ID DESC
但不考虑：

cat_link.term_taxonomy_id = cat_tax.term_taxonomy_id AND cat_tax.taxonomy NOT IN (\'category\')
如何选择未分类的记录？

2 个回复
最合适的回答,由SO网友:Paul \'Sparrow Hawk\' Biron 整理而成

如果我理解这个问题，那么您需要的SQL

$args = array (
    \'post_type\' => \'popular_music\',
    \'post_status\' => \'publish\',
    \'tax_query\' => array (
        array (
            \'taxonomy\' => \'category\',
            \'operator\' => \'NOT EXISTS\',
            ),
        ),
    \'posts_per_page\' => -1,
    \'orderby\' => \'ID\',
    \'order\' => \'DESC\',
    ) ;
$query = new WP_Query ($args) ;
echo $query->request ;
如果是，则这是WP\\U查询生成的SQL

SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.ID
FROM
    $wpdb->posts
WHERE
    1=1 AND
    ( 
        NOT EXISTS
        (
            SELECT 1
            FROM
                $wpdb->term_relationships INNER JOIN
                $wpdb->term_taxonomy ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
            WHERE
                $wpdb->term_taxonomy.taxonomy = \'category\' AND
                $wpdb->term_relationships.object_id = $wpdb->posts.ID
        )
    ) AND
    $wpdb->posts.post_type = \'popular_music\' AND
    (($wpdb->posts.post_status = \'publish\'))
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->posts.ID DESC

SO网友:Phoenix Online

您所追求的SQL与此类似：

SELECT *
FROM wp_posts AS posts
    LEFT JOIN wp_term_relationships 
        AS cat_link
        ON cat_link.object_id = posts.ID
    LEFT JOIN wp_term_taxonomy
        AS cat_tax
        ON cat_tax.term_taxonomy_id = cat_link.term_taxonomy_id
        AND cat_tax.taxonomy = \'category\'
WHERE post_type = \'post\'
AND post_status = \'publish\'
AND cat_link.object_id IS NULL

标签： posts   sql   小码农  

相关推荐

get_posts custom field

这是一个愚蠢的问题，但我找不到合适的方式问谷歌。所以，如果这是一个重复的问题，很抱歉。我提供了一个带有复选框的自定义字段，用户可以检查是否希望此特定帖子进入主页。因此，在我的主页上，我呼吁所有已激活选中的帖子。我正在为自定义帖子类型CV创建自定义字段：function add_custom_post_meta_box() { add_meta_box( \'custom_post_meta_box\', // $id \'Campos Per