Mysql Select Related Objects by Tags

Question

On a page that shows a specific object, I need to display related objects for this object based on tags. "An object with matching tags should be at the top. Basically I need to determine the number of 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

Answer 1

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);

Answer 2

Obviously, it has not been tested, 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.*)

select, , , select. , object_id COUNT (*) , .

EDIT: where , .

Answer 3

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