Geek Answers Handbook

Order items with matching tags by the number of tags that match

I am trying to figure out how to arrange items with matching tags by the number of tags that match.

Let's say you have three MySQL tables:

  • tags(tag_id, title)
  • articles(article_id, some_text)
  • articles_tags(tag_id, article_id)

Now let's say you have four articles where:

article_id = 1 has the tags "humor", "funny" and "funny".

article_id = 2 has the tags "funny", "dumb" and "dumb".

article_id = 3 has the tags "funny", "dumb" and "dumb".

article_id = 4 has the tag "completely serious".

You need to find all the articles associated with article_id = 2 with at least one matching tag and return the results in the order of best matches. In other words, article_id = 3 should be the first, and article_id = 1 second, and article_id = 4 should not be displayed at all.

Is this something that can be done in SQL queries or alone, or is it better suited for something like Sphinx? If the first, what query should be made and what indexes should be created for the most effective results? If the latter, please expand.

+7
mysql search tags sphinx
source share
2 answers

Try something like this:

 select article_id, count(tag_id) as common_tag_count from articles_tags group by tag_id where tag_id in ( select tag_id from articles_tags where article_id = 2 ) and article_id != 2 order by common_tag_count desc;

The syntax may need a little tweaking for MySQL.

or one that really works :; -)

 SELECT at1.article_id, Count(at1.tag_id) AS common_tag_count FROM articles_tags AS at1 INNER JOIN articles_tags AS at2 ON at1.tag_id = at2.tag_id WHERE at2.article_id = 2 GROUP BY at1.article_id HAVING at1.article_id != 2 ORDER BY Count(at1.tag_id) DESC;
+10
source share

Something similar:

 SELECT a.* FROM articles AS a INNER JOIN articles_tags AS at ON a.id=at.article_id INNER JOIN tags AS t ON at.tag_id = t.id WHERE t.title = 'funny' OR t.title = 'goofy' OR t.title = 'silly' AND a.id != <article_id> GROUP BY a.id ORDER BY COUNT(a.id) DESC

With regular indexes, if article_tags has PK (article_id, tag_id) and an index on tags.title

+2
source share

More articles:


All Articles