Организация облака тегов и вывод одним запросом

19 мая 2009, 20:17

Для начала заведем таблицу с именами и id самих тегов:

Table news_tags

id serial
tag character varying(128)

А затем и табличку со связями тегов и записей (блога, новостной ленты, всего-что-угодно)

Table news_taglink

news_id integer
tag_id integer

Ну и будем считать, что у нас уже есть:

новостная таблица news, где мы храним сами записи (нужное нам поле - id)
таблица рубрик(разделов) chapter (нужное нам поле - id)
таблица связей записей и рубрик news_link, которая организована точно также, как news_taglink, только с полями news_id и chapter_id

В таблицах news_link и news_taglink поля не уникальны, чтобы поддержать множественность связей записи и тегов, но в табличке news_tags для удобства поле id мы сделаем автоинкрементным, чтобы брать значение id из currval sequence для вставки новых тегов.

Запрос, вызывающий все теги и подсчет их использования заданного в переменной $cid раздела:

SELECT DISTINCT tag,count(tag_id),id,chapter_id FROM news_tags INNER JOIN news_taglink ON (id=tag_id) INNER JOIN news_link ON (news_taglink.news_id=news_link.news_id) WHERE chapter_id=$cid GROUP BY tag,id,chapter_id;

Не уверен, что запрос сработает в mysql, но в postgres все работает очень хорошо.

Если не нужно ограничение вывода тегов конкретным разделом, можно воспользоваться упрощенным запросом:

SELECT tag,count(tag_id),id FROM news_tags INNER JOIN news_taglink ON (id=tag_id) GROUP BY tag,id;

Как поддерживать теги в актуальном состоянии?

Допустим, при редактировании мы получаем теги в строке, разделенной запятыми:

$id=827; #это номер редактируемой записи
$tags="postgresql,perl";

#образуем из строки массив:
@tags=split/,/,$tags;

#удаляем старые теги для редактируемой записи:
$result=$db->do("DELETE FROM news_taglink WHERE news_id='$id';");

#и обрабатываем новый список тегов: foreach $tag(@tags) {

#для начала проверяем теги на присутствие
$result=$db->prepare("SELECT id AS tag_id FROM news_tags WHERE tag='$tag';"); $result->execute();

#если такой тег уже есть, берем его id
if ($row=$result->fetchrow_hashref()) {$tag_id=$row->{"tag_id"};}

#а если нет - вставляем его в таблицу и берем текущее значение id
else {
$result=$db->do("INSERT INTO news_tags (tag) VALUES ('$tag');");
$tag_id="currval('news_tags_id_seq')";
}

#в итоге заводим новые связи тегов и записей.
$result=$db->do("INSERT INTO news_taglink (news_id,tag_id) VALUES ('$id',$tag_id);");

}

Еще один вариант хранения тегов можно использовать только в PostgreSQL.

Используя встроенные с версии 8.3 возможности полнотекстового поиска, можно завести поле tags ключевых слов (тип tsvector) и вставлять теги туда. Поиск по тегам в таком случае сведется к запросу вида

select to_tsquery('тег') @@ tags

Tags: perl , postgresql