SQL query to retrieve all the records that have not indicated category

Question

I need to get the ID posts for which the category(taxonomy "category") is not specified. Post include other taxonomy.

I get posts from code:

$posts = $wpdb->get_results('SELECT ...', , ARRAY_A);

My query selects the desired type of recording

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

But it is not considered:

cat_link.term_taxonomy_id = cat_tax.term_taxonomy_id AND cat_tax.taxonomy NOT IN ('category')

How to select the record, Uncategorized?

Answer 1

If I understand the question, you're looking for the SQL for the equivalent of

$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 ;

If so, then this is the SQL that WP_Query produces

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

Answer 2

The SQL you're after is something similar to this:

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