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.