Related Posts in WordPress without a plugin

Post explains simple way of how to display related posts based on best matching post tags and categories. It is only needed to expand theme file single.php with SQL and final loop. In two steps you can have related posts feature without plugin installation.

First step: SQL wrapped in PHP function

function get_related_posts($id, $limit=5){
	global $wpdb; 			// enable access to the WordPress DB object
	global $related_cache;	// define cache variable
	// if related_cache has value, then return previous fetched data
	if ($related_cache) return $related_cache;
	// define SQL
	$sql = "select concat('/', p.category, '/', p.post_name, '/') as permalink, p.post_title as title, count(*) as cnt
			from wp_term_relationships tr, wp_term_taxonomy tt, wp_terms,
			( /* prepare posts and categories with smallest term_id per post */
				select p.ID, p.post_name, p.post_title, p.post_date, wp_terms.slug as category
				from wp_posts p, wp_term_relationships tr, wp_term_taxonomy tt, wp_terms
				where
				p.ID               != $id                 and /* exclude observed post */
				p.post_type         = 'post'              and
				p.post_status       = 'publish'           and
				p.ID                = tr.object_id        and
				tr.term_taxonomy_id = tt.term_taxonomy_id and
				tt.taxonomy         = 'category'   and
				tt.term_id          = wp_terms.term_id
				group by p.ID, p.post_title, p.post_name, p.post_date
				order by wp_terms.term_id
			) p,
			( /* fetch 'post tags' and 'category' for counting */
				select distinct wp_terms.slug
				from wp_term_relationships tr, wp_term_taxonomy tt, wp_terms
				where
				tr.object_id        = $id                 and
				tr.term_taxonomy_id = tt.term_taxonomy_id and
				tt.taxonomy in ('post_tag', 'category')   and
				tt.term_id          = wp_terms.term_id
			) tg
			
			where
			p.ID                = tr.object_id        and
			tr.term_taxonomy_id = tt.term_taxonomy_id and
			tt.taxonomy in ('post_tag', 'category')   and
			tt.term_id          = wp_terms.term_id       and
			wp_terms.slug       = tg.slug
			
			group by p.post_title, p.post_name, p.category
			
			/* order related posts with max 'post tags' count first */
			order by cnt desc, p.post_date desc
			limit $limit";
	// execute SQL and save result set to the related_cache variable
	$related_cache = $wpdb->get_results($sql);
	// return result set as object
	return $related_cache;
}

SQL query is optimised for permalinks with format /%category%/%postname%/. In case of different format, you will have to customize SQL and PHP code.

Second step: Show 5 related posts

<h3>Related posts</h3>
<ul>
  <?php 
    // fetch 5 related posts
    $related_posts = get_related_posts($post->ID, 5);
    // open loop
    foreach ($related_posts as $related_post) {
      $permalink = $related_post->permalink;
      $title     = $related_post->title;
      print "<li><a title=\"$title\" href=\"$permalink\">$title</a></li>\n";
  } ?>
</ul>

Related posts can be displayed at the post bottom. First parameter is ID of the shown post while second parameter is number of related posts to show. Main SQL might seems long but it has two subqueries to prepare post permalink and to count tags of other posts. Anyway, as you can see, described method works great on my blog. Cheers!

3 thoughts on “Related Posts in WordPress without a plugin

  1. With some knowledge of SQL, it’s possible to retrieve almost any data out of WordPress. Data model is simple and it’s easy to join tables in SQL query. Hope this code will give you an example how to easily display related posts. Thanks!

Leave a Comment