Only show posts with attachments in a loop

Posted on June 29, 2011 by admin

Last night when I was working on a redesign of one of my clients website. The idea was to show a thumbnail and title for 4 random posts. Easy enough I thought. Just wp_Query that stuff and in the loop but the code to retrieve the image. However, that didn’t work the way I wanted. Since some of the posts don’t have an image.

Therefore I had to come up with a different solution. After googling around I came across this forum topic. http://wordpress.org/support/topic/exclude-posts-without-attachments-from-query

<?php
global $wpdb;
$posts = $wpdb->get_results
("
  SELECT *
  FROM $wpdb->posts
  WHERE
      post_status = 'publish'
    AND
      ID IN (
	SELECT DISTINCT post_parent
	FROM $wpdb->posts
	WHERE
	  post_parent > 0
	AND
	  post_type = 'attachment'
	AND
	  post_mime_type IN ('image/jpeg', 'image/png')
      )
    ORDER BY post_date DESC
");
 
foreach($posts as $post) :
  setup_postdata($post);
/* POST THE CODE FOR THE LOOP HERE */
endforeach;
wp_reset_query(); ?>

Showing me how to use MySQL to only loop posts with an image. And leave out the the ones that don’t contain an attachment. It worked like a charm. However I had to make a couple of alterations to the code, since I wanted to show posts only from a specific category. And because the WordPress Database uses a different table for posts and categories a had to find a way to link the tables. Since my knowledge of MySQL is very poor, I had to head back to google again. After a little search I came across this link.

http://wordpress.org/support/topic/mysql-query-for-all-posts-in-news-category-id-problem

SELECT * FROM wp_posts p
LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND t.slug = 'news'

Showing me how to link the tables. So now the only thing I had to do is combine everything and put in a php variable for post category.

<?php 
 
global $wpdb;
$posts = $wpdb->get_results
("
  SELECT *
 FROM $wpdb->posts p
 LEFT OUTER JOIN $wpdb->term_relationships r ON r.object_id = p.ID
 LEFT OUTER JOIN $wpdb->term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
 LEFT OUTER JOIN $wpdb->terms t ON t.term_id = x.term_id
  WHERE
      p.post_status = 'publish'
    AND
	x.parent = '7'          /* Only show posts in this category or it's children */
    AND
      ID IN (
	SELECT DISTINCT p.post_parent
	FROM $wpdb->posts p
	WHERE
	  p.post_parent > 0
	AND
	  p.post_type = 'attachment'
	AND
	  p. post_mime_type IN ('image/jpeg', 'image/png')
      )
 
    ORDER BY rand() DESC     /* Order the posts randomly */
	LIMIT 0,4      /* Only get 4 posts */
 
");
 
foreach($posts as $post) :
  setup_postdata($post);
 
         /* PLACE THE CODE FOR YOUR LOOP HERE */
endforeach;
wp_reset_query(); ?>

Now it searches through the database, checking if a post is published, if it’s parents category is number 7 (you can change this to whatever fits your needs) and if the post has an jpeg or png attached to it. 
Then it displays them randomly and limits it to 4 posts. You can change that in whatever you need.

Please note : I used x.parent = '7' because I wanted to get the posts in all subcategories of category 7. You can also use t.term_id = '7' if you only need to display the posts in that particular category, without looking at it’s subcategories.

I also wanted to use this code to show 4 random posts in the same category as the current post in the single template but excluding the current post. After finding all of the above, this was a breeze. All i had to do is create a variable to retrieve the current post ID and current category ID. That total piece of code looks like this.

<?php
/* GET the current category ID */
$category = get_the_category();
$category_id = $category[0->cat_ID;
 
/* GET the current Post ID */
global $wp_query;
$thePostID = $wp_query->post->ID;
 
global $wpdb;
$posts = $wpdb->get_results
("
  SELECT *
 FROM  $wpdb->posts p
 LEFT OUTER JOIN  $wpdb->term_relationships r ON r.object_id = p.ID
 LEFT OUTER JOIN  $wpdb->term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
 LEFT OUTER JOIN  $wpdb->terms t ON t.term_id = x.term_id
  WHERE
      p.post_status = 'publish'
    AND
	t.term_id = '".$category_id."' /* Only show posts if in current category */
    AND
	p.ID != '".$thePostID."' /* Don't show the current post */
    AND
      ID IN (
	SELECT DISTINCT p.post_parent
	FROM  $wpdb->posts p
	WHERE
	  p.post_parent > 0
	AND
	  p.post_type = 'attachment'
	AND
	  p. post_mime_type IN ('image/jpeg', 'image/png')
      )
 
    ORDER BY rand() DESC      /* Order the posts randomly */
	LIMIT 0,4    /* Only get 4 posts */
 
");
 
foreach($posts as $post) :
  setup_postdata($post);
 
/* PLACE THE CODE FOR YOU LOOP HERE */
 
endforeach;
wp_reset_query(); ?>

This might not be a very clean solution. But it works like a charm, so I’m happy. Hopefully the WordPress Developers will find a way to incorporate something like this in a future version of WordPress.

I’m not really a developer, I just like to find a solution to the problems I encounter when working with WordPress. So if anyone knows a better solution please share it in the comment section below.

Posted in Code, Wordpress | Tagged , , | Leave a comment