php - Search for matching tags (Similarity Search) with MySQL and Constraints

Question

this is my first thread. I apologize if this is a duplicate, but then i was not able to search for the right keywords.

My Problem: There are tagged items on a website. On a detailed view of a item e.g. http://localhost/items/a.html i want to show a list of similar/related items based on their tags.

• Item A-Tags

  • Labor
  • Chemie
  • Analytik
  • Mehrweg

• Item B-Tags

  • Labor
  • Chemie
  • quantitativ
  • Laktose

• Item C-Tags

  • quantitativ
  • abscheiden
  • Flaschenpfand

The Structure of the tables: (as in here)

Table: article Fields: articleid, title

Table: tag Fields: tagid, tagtext

Table: articletag Fields: tagid, articleid

When I now visit following entry point (http://localhost/items/b.html) for item B i want to print a list to the user which show the related items in this order:

• Item A (because of two same tags)
• Item C (because of one same tag)

As i searched in google i found something like this: How to compare two comma-separated string lists using MySQL

It looks just as my problem, but i didn't see any hints how to solve that for my query. The most other post that i found would just exclude item a and c because they dont match all of the tags (item a doesnt match for the tag qantitativ and Laktose)

Which further information do you need? Btw: I will not add all tags as a extra property to the items table due to be able to perform a match against on this column. I just don't want to update this tags always when the tags changes. This is just doubling the size of saved data in the db.

Answer 1

SELECT  c.articleid, COUNT(*) AS ct
    FROM articletag AS b
    JOIN articletag AS c  ON c.tagid = b.tagid
                         AND c.articleid != b.articleid
    WHERE b.articleid = 123
    GROUP BY c.articleid
    ORDER BY ct DESC;

When searching for what is 'similar' to 123 (Item-B in your example), the output should

Item-A, 2
Item-C, 1

This is a full scan of articletag. So, heed the tips in my discussion of many:many mapping .

If you need to get information about the articles after performing the query, use it as a 'derived' table; for example:

SELECT articles.*
    FROM ( the above SELECT ) AS x
    JOIN articles USING(articleid)
    ORDER BY x.ct DESC;

(You can remove the ORDER BY from the inner query, since it will be ignored in preference to the outer ORDER BY.)