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

by

I want to get a list of all posts that don’t have an attachment and delete them.

This question takes about getting all posts that have an attachment, but I want the reverse of it.

The brute force way to do it is to get all posts and then loop through them individually and then check if they have attachement or not. But I want to avoid it if possible.

2 s
2

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