How to Create MySQL Query to Find Related Posts from Multiple Tables?

Question

This is a complicated situation (for me) that I'm hopeful someone on here can help me with. I've done plenty of searching for a solution and have not been able to locate one. This is essentially my situation... (I've trimmed it down because if someone can help me to create this query I can take it from there.)

TABLE articles (article_id, article_title)

TABLE articles_tags (row_id, article_id, tag_id)

TABLE article_categories (row_id, article_id, category_id)

All of the tables have article_id in common. I know what all of the tag_id and category_id rows are. What I want to do is return a list of all the articles that article_tags and article_categories MAY have in common, ordered by the number of common entries.

For example:

article1 - tags: tag1, tag2, tag3 - categories: cat1, cat2

article2 - tags: tag2 - categories: cat1, cat2

article3 - tags: tag1, tag3 - categories: cat1

So if my article had "tag1" and "cat1 and cat2" it should return the articles in this order:

article1 (tag1, cat1 and cat2 in common)

article3 (tag1, cat1 in common)

article2 (cat1 in common)

Any help would genuinely be appreciated! Thank you!

Answer 1

Okay, here's my first draft:

 SELECT article_id, count(*) as common_term_count FROM 
      (
      SELECT article_id FROM tags WHERE tag IN 
         (SELECT tag FROM tags WHERE article_id = :YourArticle)
      UNION ALL 
      SELECT article_id FROM categories WHERE category IN
         (SELECT category FROM categories WHERE article_id = :YourArticle)
       ) AS accumulator_table 
 GROUP BY article_id ORDER common_term_count DESC

I think this is valid MySQL syntax.

Answer 2

If you would just have the tag table (not the category table) this is a more optimized start:

SELECT article_id,count(*) AS q FROM article_tags WHERE id_tag IN (SELECT id_tag FROM article_tags WHERE article_id=41) AND article_id!=41 GROUP BY article_id ORDER BY q DESC