mysql Select related objects by tags

On a page which shows a specific object I need to display related objec开发者_开发知识库ts for that object based on tags. ´The object with the most matching tags should be at the top. Basically I somhow need to identify how many tags for each object that match the object on the page and display the top results.

My db schema:

Table Object
------------
  id
  name


Table Tagset
-------------
  object_id
  tag_id


Table Tag
------------
  id
  name

---

This should do what you want:

SELECT object.name, COUNT(*) AS tag_count
FROM tagset T1
JOIN tagset T2
ON T1.tag_id = T2.tag_id AND T1.object_id != T2.object_id
JOIN object
ON T2.object_id = object.id
WHERE T1.object_id = 1
GROUP BY T2.object_id
ORDER BY COUNT(*) DESC

Result:

'object2', 2
'object3', 1

Using this test data:

CREATE TABLE object (id int NOT NULL, name nvarchar(100) NOT NULL);
INSERT INTO object (id, name) VALUES
(1, 'object1'),
(2, 'object2'),
(3, 'object3');

CREATE TABLE tagset (object_id int NOT NULL, tag_id int NOT NULL);
INSERT INTO tagset (object_id, tag_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 3),
(3, 2),
(3, 4),
(3, 5);

---

Obviously untested, but it should work or at least get you on the right track:

SELECT ts.object_id, o.name, COUNT(ts.*) as matching_tags
FROM Tagset ts
INNER JOIN
    (SELECT tag_id
     FROM Tagset
     WHERE object_id = <your object id>) otags ON ts.tag_id = otags.tag_id
INNER JOIN Object o ON ts.object_id = o.id
WHERE ts.object_id <> <your object id>
GROUP BY ts.object_id, o.name
ORDER BY COUNT(ts.*)

Basically we start with creating a select statement that will get all tags that your object belongs to, then we use that as a derived table and join it to filter out any other tags in the outer select. Finally we group on the object_id and do a COUNT(*) on the tagset table to find out many matching tags there are.

EDIT: forgot the where clause on the outside to get rid of your source object.

---

select t.id, t.name
from tag t
inner join
(
  select tag_id, count(tag_id) as counttags
  from tagset
  where object_id = <some expression to indicate the current object id>
  group by tag_id
) g
  on t.id = g.tag_id
  order by g.counttags desc