Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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;
}
share|improve this answer
    
oh, I didn't notice the SQL update in the question. Looks very similar. @toscho's version looks more streamlined ;-) –  birgire Aug 25 at 10:43
2  
I crashed PHPMyAdmin few times, when playing with GROUP BY so I used DISTINCT instead ;-) –  birgire Aug 25 at 10:52
3  
Can't upvote you again unfortunately :-). But I like your second approach better. –  Pieter Goosen Aug 25 at 11:25
1  
@PieterGoosen thanks, and it's less code ;-) @Sudar I added one more way with a LEFT JOIN instead of a sub query, for some reason I'm always sceptical of sub queries ;-) –  birgire Aug 25 at 12:33
1  
@birgire Thanks and I have marked this as the answer. I like the 2nd and 3rd approach. –  Sudar Aug 25 at 16:52

If you're comfortable with the complete opposite of the linked answer, you simply could use this query to fetch all posts that have an attachment and then use their IDs as the post__not_in parameter of \WP_Query:

$attachment_args = array( 
  'post_type'      => 'attachment',
  'post_mime_type' => 'image',
  'post_status' => 'inherit',
  'posts_per_page' => -1,
  'post_parent__not_in' => array(0),
  'meta_query' => array(
    array(
      'key' => '_thumbnail_id',
      'value' => 'x',
      'compare' => 'NOT EXISTS'
    )
  ),
  'fields' => 'post_parent'
);
$atts = new WP_Query($args);
$parents = array_unique(wp_list_pluck($atts->posts,'post_parent'));

$post_args = array(
    'post_type'      => 'post',
    'posts_per_page' => -1
    'post__not_in'   => $parent 
    'post_status'    => 'any'
);
// Posts with no attachment:
$post_query = new WP_Query( $post_args );

Update Toscho pointed me to make this one query. And, of course, this can be handled with one plain SQL Query:

<?php 
$query = <<<SQL
    SELECT p.`ID` FROM {$wpdb->posts} p 
    WHERE p.`post_type` = 'post'
    AND p.`post_status` = 'publish'
    AND p.`ID` NOT IN (
        SELECT DISTINCT a.`post_parent` FROM {$wpdb->posts} a 
        WHERE a.`post_type` = 'attachment'
        AND a.`post_parent` != 0
    )
SQL;

//posts with no attachment
$results = $GLOBALS[ 'wpdb' ]->get_results( $query );

Note that this is slightly different to the variant taken from the referred answer as this query make no difference between an image and a post-thumbnail and also looks for any type of attachments.

share|improve this answer
    
Thanks. I generally try to use WP_Query instead of writing my own SQL statements as it is the recommended way. But I guess this is a complex enough scenario to go the SQL statement route. –  Sudar Aug 25 at 10:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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