Vitaphoto: Облака тегов

Ссылки сюда (3) →

Облака тегов — набор ссылок, расположенных кластерно или хаотически с размером каждой ссылки, зависящим от её «важности». Конкретнее, в случае «тегов» (меток), каждая ссылка ведёт на все элементы, имеющие данный тег, а размер ссылки зависит от количества таких элементов. Таким образом, наиболее популярные метки оказываются крупными, а непопулярные — мелкими.

Однако, классические, «тупые» облака тегов — обычно свалка в духе «фигпоймёшь», что и где, особенно, если тегов много. (в одном моём Vitaphoto их больше 250…) Но сама-то идея — замечательная! И в Vitaphoto хочется заменить облаками тегов навигацию по альбомам. Как это сделать?

Итак, идея: часто бывает, что какие-то теги являются «подтегами» других. Например, все фотографии с людьми помечены тегом «люди», одновременно некоторые из них помечены тегом с именем конкретного человека. Или же: все фотографии с какой-нибудь тусовки-выезда помечены тегом «Тусовка-выезд 2009», при этом внутри неё тоже существовали «подмероприятия», например, «Экскурсия на Фиолетовые Холмы», и тегами с такими названиями помечены соответствующие множества фотографий. Зачем показывать эти «подтеги», к примеру, на главной странице? Не нужно это.

Правда, может быть ситуация, когда два тега А и Б встречаются на фотографиях только вместе (возникает, конечно, вопрос, нафига ж их тогда два, а не один?) — в этом случае, по-видимому, должны включаться оба.

А при переходе «внутрь» какого-то тега — можно сужать множество тегов облака до тегов, встречающихся вместе с ним.

SQL

А теперь вопрос: как это реализовать средствами MySQL? :)

Есть таблица-отношение tag_image с двумя полями «ID тега» (ti_tag) и «ID изображения» (ti_image). Каждая строчка означает, что фотография с соответствующим ID имеет соответствующий тег.

Ответ ужасен:

SELECT DISTINCT tp_tag1
FROM (SELECT DISTINCT t1.ti_tag tp_tag1, t2.ti_tag tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair
JOIN (SELECT t1.ti_tag tpi_tag1, t1.ti_image tpi_image, t2.ti_tag tpi_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair_image
ON tpi_tag1=tp_tag1 AND (tpi_tag2!=tp_tag2 OR tp_tag2 IS NULL)
 
UNION
 
SELECT DISTINCT ti_tag FROM tag_image WHERE ti_tag NOT IN (
  SELECT tp_tag1 FROM (SELECT DISTINCT t1.ti_tag tp_tag1, t2.ti_tag tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair
  LEFT JOIN tag_image h1 ON h1.ti_tag IN (tp_tag1, tp_tag2)
  LEFT JOIN tag_image n1 ON n1.ti_image=h1.ti_image AND n1.ti_tag != h1.ti_tag AND n1.ti_tag IN (tp_tag1, tp_tag2)
  WHERE n1.ti_tag IS NULL
)

Самое интересное, что если таблицы tag_pair и tag_pair_image материализовать и поддерживать в актуальном состоянии, такое решение выполняется за вполне приемлемое время.