Articles By Related Tags

[ShoeLace1291]

So, I have a database with a table called artcles, and also a table called article tags. When a user views an article, I want to query up to five articles that have tags similar to the one that is being viewed. Here are my two tables:

 

table: articles
- article_id  // Primary, unique, auto increment
- title
- content

table: article_tags
- tag_id // Primary, unique, auto increment
- article_id //Relational to the above table
- keyword

 

I've tried writing my own queries, but they never seem to work. I would like to use joins in the query instead of resorting to using CSV's and LIKE. Any help would be appreciated!

[ShoeLace1291]

This is what my tables actually look like:

 

CREATE TABLE `articles` (
`article_id` int(15) NOT NULL AUTO_INCREMENT,
`parent_id` int(15) NOT NULL,
`author_id` int(15) NOT NULL,
`title` text NOT NULL,
`content` text NOT NULL,
`date_posted` text NOT NULL,
`views` int(15) NOT NULL,
`preview` text NOT NULL,
`status` tinyint(1) NOT NULL,
`modified_date` text NOT NULL,
PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article_tags` (
 `tag_id` int(15) NOT NULL AUTO_INCREMENT,
 `article_id` int(15) NOT NULL,
 `keyword` varchar(250) NOT NULL,
 PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

 

This is the query I have written so far, and it does not work...

 

SELECT A2.article_id, count(A2.article_id) AS matches
 FROM article_tags AS A1 JOIN article_tags ON (A1.keyword = A2.keyword AND A1.article_id != A2.article_id)
 JOIN articles ON (A2.article_id = A.article_id) AS A
 WHERE A1.article_id = 1
 GROUP BY A2.article_id
 ORDER BY matches DESC
 LIMIT 5

Edited September 29, 2012 by ShoeLace1291

[ignace]

This article explains how you can query posts having similar tags (look at #3 and #4):

http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/

Edited September 29, 2012 by ignace

[Barand]

First, find the tags of the chosen article id ($id)

 

SELECT tag_id
FROM article_tags
WHERE article_id = $id

 

Then join to this (as a table subquery) on tag_id to find the articles

 

SELECT a.article_id, COUNT(tag_id) as matches
FROM article_tags a
INNER JOIN
    (
    SELECT tag_id
    FROM article_tags
    WHERE article_id = $id
    ) as b
    ON a.tag_id = b.tag_id
WHERE a.article_id <> $id
GROUP BY a.article_id
ORDER BY matches DESC
LIMIT 5