How to Select Related Posts (by tags and category relevance) in WordPress

  1. The first step is to get the post ID of the blog post you are viewing. You can get the id of a post by using $post->ID in WordPress. Let’s pretend it’s 3987.
  2. You will also need to know the term ids for categories and tags associated with this blog post

Here is some PHP code you can use in wordpress to get the tags of your post ID.

 $tags = wp_get_post_tags( $post->ID );

Let’s assume the result is one category (264) and one tag (41).

  1. If you are not using WordPress Mu, you can simply select from wp_term_relationships and wp_posts
  2. If you are using WordPress Mu, you will need to specify the tables as wp_MU-BLOG-ID#_term_relationships and wp_MU-BLOG-ID#_posts. Let’s assume we are using WordPress Mu and our blog ID is 1. The tables we are working with are wp_1_term_relationships and wp_1_posts
SELECT p.ID, COUNT(tr.object_id) AS cnt 
FROM wp_1_term_relationships AS tr, wp_1_posts AS p 
WHERE tr.object_id = p.id 
AND tr.term_taxonomy_id IN(264,41) 
AND p.id!=3987 
AND p.post_status='publish'
GROUP BY tr.object_id ORDER BY cnt DESC, p.post_date_gmt DESC LIMIT 5;

Notice that we have limited our results to the 5 most relevant posts. It is recommended that you limit this query because it can be a difficult query to process if you are running it on a blog that has thousands of posts. You can change LIMIT 5 to LIMIT 25 or so on blogs that are hosting less than 5,000 posts without seeing much of a difference in performance. On larger, more frequently updated blogs or WordPress MU sites which are hosting a lot of blogs on the same server, it is recommended that you cache the results of this query and only use it once an hour or once every time a blog entry has been updated.

Tagged with: , , , ,
Posted in uncategorized misfits