mysql tag system

1 Views

I need some help with a mysql query

I am using wordpresses tagging system with some tables of my own design

Read More

the idea is to pull back a list of items that have tags in common with the original post, I will try my best to explain

let say you have a post with 5 tags tagA tagB tagC and so on

I want to run a query on mysql that would pull back a list ordered by how many tags the other items have in common

I imagine it would create a new column based on a count of tags that are associated with the original post.

so if one post had the same 5 tags I would see post_id and 5 in the new column 

| post_id | in_common |
-----------------------
|   35    |     5     |
-----------------------
|   109   |     5     |
-----------------------
|   100   |     4     |

and so on.

But I’m not even sure where to start with this query

The tags are in wordpresses and to get all the posts based on one single tag I could use 

SELECT * 
FROM wp_posts 
inner JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_ID 
inner JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id 
WHERE wp_terms.name =  'tax'
Post Views: 1

Related posts

Leave a Reply

1 comment

  1. As you mentioned wordpress I guess you’re using PHP. So I’d suggest to use PHP to build a comma separated (and properly escaped) list of the term_taxonomy_ids of your current post and then use a simple query like this to get a list of all posts having tags in common:

    SELECT wp_posts.ID, COUNT(*)
FROM wp_posts
JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_ID 
WHERE term_taxonomy_id IN ('.$the_list.')
GROUP BY wp_posts.ID