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