1

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?

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
2
  • How did you get SQL from WP_Query? – TrubinE Feb 20 '17 at 7:46
  • I edited my answer to to add the echo $query->request ; statement I used to get the SQL. – Paul 'Sparrow Hawk' Biron Feb 20 '17 at 14:25
0

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
11
  • AND cat.term_taxonomy_id NOT IN (12,46,33) - Not suitable as the category a lot and they are constantly being added AND cat.object_id IS NULL"; - return zero results – TrubinE Feb 19 '17 at 19:40
  • AND cat.object_id IS NULL"; - return zero results, because it belongs to another post taxonomy – TrubinE Feb 19 '17 at 19:43
  • Thanks for the additional info, have you tried the AND cat.object_id IS NULL query without the other 2 WHERE conditions, or at the very least, with the post_type clause set to your custom post type (eg WHERE post_type = 'product' or WHERE post_type = 'staff'? – Phoenix Online Feb 19 '17 at 19:45
  • I tried to use each option separately – TrubinE Feb 19 '17 at 19:48
  • Try eliminating all unnecessary conditions from the WHERE clause, then. eg SELECT posts.* FROM wp_posts AS posts LEFT JOIN wp_term_relationships AS cat ON cat.object_id = posts.ID WHERE AND cat.object_id IS NULL That should prove the SQL works; then all you have to do is add in a condition that narrows to the custom post type you're after. – Phoenix Online Feb 19 '17 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.