Is it possible to query all posts that don’t have an attachment?

by

I got curious about the SQL way of finding all posts without any attachments.

Method #1 – Sub query with NOT IN

Here’s my first attempt to construct such a query:

global $wpdb;            
$sql = "
    SELECT  p1.ID, p1.post_title         
    FROM    {$wpdb->posts} p1
    WHERE   p1.post_type="post"
        AND p1.post_status="publish" 
        AND p1.ID NOT IN ( 
                SELECT DISTINCT p2.post_parent
                FROM {$wpdb->posts} p2
                WHERE p2.post_type="attachment" AND p2.post_parent > 0  
        ) 
    ORDER BY p1.post_date DESC
";

// Fetch posts without attachments:
$posts_without_attachments = $wpdb->get_results( $sql );

// Display posts without attachments:
foreach( $posts_without_attachments as $post )
{
        echo $post->post_title . '<br/>';
}

This happens to be very similar to @toscho’s query, but less streamlined in the syntax 😉

Method #2 – LEFT JOIN with IS NULL

This query seems to work as well:

global $wpdb;            
$sql = "
    SELECT  p1.ID, p1.post_title
    FROM {$wpdb->posts} p1 
    LEFT JOIN {$wpdb->posts} p2 
    ON ( p2.post_parent = p1.ID AND p2.post_type="attachment" )
    WHERE p1.post_type="post" 
    AND p1.post_status="publish"
    AND p2.post_parent IS NULL 
    ORDER BY p1.post_date DESC
";

// Fetch posts without attachments:
$posts_without_attachments = $wpdb->get_results( $sql );

where we join the posts table with itself and then pick up the NULL rows in the attachments’ parent column.

Method #3 – WP_Query with posts_where filter aka method #1

We could also modify the WP_Query() with the posts_where filter:

// Filter all posts without attachments:
add_filter( 'posts_where', 'wpse_no_attachments' );

// Query:
$q = new WP_Query( array( 'post_type' => 'post', 'posts_per_page' => -1 ) );

// Remove the filter:
remove_filter( 'posts_where', 'wpse_no_attachments' );

where:

function wpse_no_attachments( $where )
{
    global $wpdb;
    $where .= " AND {$wpdb->posts}.ID NOT IN (
                    SELECT DISTINCT wpse.post_parent
                    FROM {$wpdb->posts} wpse
                    WHERE wpse.post_type="attachment" AND wpse.post_parent > 0  ) ";
    return $where;
}

Leave a Comment