Have Geek Brain

How do I list related blog posts sorted by the number of shared tags?

I want to display a list of related blog posts and I want the list to be ordered by the number of common tags they have in the current post. Each post can have multiple tags associated with it. Here is my table structure:

[Posts] <- [posts-to-tags-append-table] β†’ [Tags]

I am using PHP and MySQL - can I do it in one query?

+1


source to share


2 answers


What about...:

SELECT COUNT(*) AS numcommon, posts.pid, posts.post FROM posts
               INNER JOIN p2t ON p2t.pid = posts.pid
               WHERE p2t.tid IN
               (SELECT p2t.tid FROM p2t
               INNER JOIN posts ON p2t.pid = posts.pid
               WHERE posts.pid = 1)
               AND posts.pid != 1
               GROUP BY posts.pid
               ORDER BY numcommon


Assuming pid is the primary key in the post table, as the primary key in the tags table, how are foreign keys in the p2t table (post to tag)?

0


source


You can of course do it in one query:



SELECT postid, count(tagid) as common_tag_count
FROM posts_to_tags
WHERE tagid IN (SELECT tagid FROM posts_to_tags WHERE postid = 2)
GROUP BY postid ORDER BY common_tag_count DESC;
+5


source






More articles:


All Articles