CODEWITHSUNDEEP
mysqldatabase
iBrazilian2
iBrazilian2

Reputation: 2293

How to get related articles by tag names?

I have two tables, one that are connected to the first with a n:1 relationship.

I am trying to retrieve related articles by tag names inserted when the articles are posted.

My table structure stands as followed:

articles

id  |  article_title  | article_content
----+-----------------+----------------
 1  |   title one     | info goes here
 2  |   title two     | info goes here
 3  |   title three   | info goes here

article_tags

tag_id |  article_id  | tag_name
-------+--------------+----------------
   1   |     1        | health
   2   |     1        | information
   3   |     2        | how-to
   4   |     3        | how-to
   5   |     3        | health
   6   |     3        | network
   7   |     1        | network

I am unable to figure out how to get related articles by those tag_names for example.

article_id 1 has health which article 3 also has in common.

article_id 2 has how-to which article 3 also has in common.

I have tried the following but it doesn't really relate to the tag names as I couldn't figure out how to connect the tag_names..

SELECT *
FROM articles 
LEFT JOIN article_tags ON articles.article_uid = article_tags.article_id
LIMIT 4

Upvotes: 0

Views: 78

Answers (1)

Barmar
Barmar

Reputation: 780994

SELECT a1.id, GROUP_CONCAT(DISTINCT a2.id) AS related_articles
FROM articles AS a1
JOIN article_tags AS t1 ON a1.id = t1.article_id
JOIN article_tags AS t2 ON t2.tag_name = t1.tag_name AND t2.article_id != t1.article_id
JOIN articles AS a2 ON a2.id = t2.article_id
GROUP BY a1.id

DEMO

Upvotes: 3

Related Questions