如何创建MySQL查询以从多个表中查找相关帖子? [英] How to Create MySQL Query to Find Related Posts from Multiple Tables?

问题描述

(对我来说)这是一个复杂的情况，我希望在座的人可以帮助我.我已经做了很多寻找解决方案的工作，却找不到一个解决方案.基本上这就是我的情况...(我已经将其修整了，因为如果有人可以帮助我创建此查询，我可以从那里获取它.)

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.)

表格文章(article_id，article_title)

TABLE articles (article_id, article_title)

表articles_tags(row_id，article_id，tag_id)

TABLE articles_tags (row_id, article_id, tag_id)

表article_categories(row_id，article_id，category_id)

TABLE article_categories (row_id, article_id, category_id)

所有表都具有相同的article_id.我知道所有的tag_id和category_id行都是什么.我要做的是返回article_tags和article_categories可能共有的所有文章的列表，并按常见条目的数量排序.

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.

例如:

article1-标签:tag1，tag2，tag3-类别:cat1，cat2

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

article2-标签:tag2-类别:cat1，cat2

article2 - tags: tag2 - categories: cat1, cat2

article3-标签:tag1，tag3-类别:cat1

article3 - tags: tag1, tag3 - categories: cat1

因此，如果我的文章包含"tag1"和"cat1 and cat2"，则应按以下顺序返回文章:

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

article1(tag1，cat1和cat2共有)

article1 (tag1, cat1 and cat2 in common)

article3(标记1，共同使用cat1)

article3 (tag1, cat1 in common)

article2(常见于cat1)

article2 (cat1 in common)

任何帮助将不胜感激！谢谢！

Any help would genuinely be appreciated! Thank you!

推荐答案

好的，这是我的初稿:

 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

我认为这是有效的MySQL语法.

I think this is valid MySQL syntax.

这篇关于如何创建MySQL查询以从多个表中查找相关帖子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！