Как добавить ещё одну выборку SQL JOIN?

Question

[ТемноКот]

Доброго времени суток, уважаемые!
Имеется банальная галерея, собираемая из нескольких таблиц MySQL: Категория, Рисунки, Тэги.
Рисунки и Тэги, как "многие к многим", собираются через дополнительную таблицу images_tags

Таблицы

таблица category
| category_id | title   |
-------------------------
|     1       | люди    |

таблица images
 id |  name    | category_id |
 -----------------------------
 1  | Иван     |     1       |
 2  | Марья    |     1       |

таблица tags
id | tagname |
--------------
1  | человек |
2  | женщина |
3  | мужчина |

таблица images_tags
 id |  images_id   | tags_id     |
 ----------------- ---------------
 1  | 1 (Иван)     | 1 (человек) |
 2  | 2 (Марья)    | 1 (человек) |
 3  | 1 (Иван)     | 3 (мужчина) |
 4  | 2 (Марья)    | 2 (женщина) |

Выводится всё это через SQL INNER JOIN

$query = '
SELECT images.name,
 GROUP_CONCAT(tags.tagname SEPARATOR ", ") AS tagname,
 category.title
FROM images
INNER JOIN images_tags ON images.id = images_tags.images_id
INNER JOIN tags ON images_tags.tags_id = tags.id
INNER JOIN category ON images.category_id = category.category_id
WHERE images.category_id = 1
GROUP BY images.name
';
$query = $pdo->query($query);
$arrTable = $query->fetchAll(PDO::FETCH_ASSOC);

На выходе получаем массив $arrTable:

массив $arrTable

Array
(
  [0] => Array
    (
      [name] => Иван
      [tagname] => Человек, Мужчина
      [title] => Люди
    )

  [1] => Array
    (
      [name] => Марья
      [tagname] => Человек, Женщина
      [title] => Люди
    )
)

Но надо ещё получить список всех ТЭГОВ, из таблицы tags, соответствующий категории "Люди" (чтобы вывести на боковую панель "Облаком тегов" категории).
То есть дополнительно получить список:
Человек
Женщина
Мужчина

ВОПРОС: Как это сделать?
Создать ещё один запрос в tags.tagname?
Или взять полученный массив $arrTable вытащить оттуда $arrTable[tagname], пересобрать, почистить от дубликатов и выводить уже его?

Comments

[Akina]

надо ещё получить список всех ТЭГОВ, из таблицы tags, соответствующий категории "Люди"

Таблицы тегов и категорий не связаны. В текущей постановке задача нерешаема.
Можно получить только список использованных тегов.

ВОПРОС: Как это сделать?
Создать ещё один запрос в tags.tagname?

Несомненно. Имеющийся и требуемый наборы данных - не связаны.

[ТемноКот]

Простите, но список использованных тегов как раз и нужно получить, потому как он принадлежит нужной категории (в запросе же указано WHERE images.category_id = 1 где как раз айдишник категории).

Можно, наверное, в связующую таблицу images_tags добавить столбец cat_id куда вносить значение из images.category_id или создать ещё одну связующую таблицу category_tags, где связать теги с категориями, но нужно ли?

Answer 1

[alexalexes]

Нужно подзапросом пройтись опять по той же структуре таблиц, и сгруппировать теги относительно категории.
Атрибут category_tagname покажет все теги, используемые в изображениях той же категории.

SELECT images.name,
 GROUP_CONCAT(tags.tagname SEPARATOR ", ") AS tagname,
 category.title,
 a.category_tagname
FROM images
INNER JOIN images_tags ON images.id = images_tags.images_id
INNER JOIN tags ON images_tags.tags_id = tags.id
INNER JOIN category ON images.category_id = category.category_id
---------------------
join (SELECT category.category_id,
                    GROUP_CONCAT(tags.tagname SEPARATOR ", ") AS category_tagname
          FROM images
              INNER JOIN images_tags ON images.id = images_tags.images_id
              INNER JOIN tags ON images_tags.tags_id = tags.id
              INNER JOIN category ON images.category_id = category.category_id
               GROUP BY category.category_id
) a on a.category_id = images.category_id
----------------------
WHERE images.category_id = 1
GROUP BY images.name, category.title, a.category_tagname

Или взять полученный массив $arrTable вытащить оттуда $arrTable[tagname], пересобрать, почистить от дубликатов и выводить уже его?

Можно и на стороне php дообработать выборку. На практике, обычно, смотрят что либо быстрее выполняется, либо меньше по памяти.

Comments

[ТемноКот]

Попробовал ваш скрипт. Не получилось почему-то. [category_tagname] вообще отсутствует в массиве выборки.

Да и как-то уж слишком монструозно всё получается с этими вложенными селектами. Имхо, проще функцию написать, которая $arrTable[tagname] соберёт, почистит от дубликатов и выведет.

Попробовал вот так

$query = '
SELECT images.name,
GROUP_CONCAT(tags.tagname SEPARATOR ", ") AS tagname,
category.title,
(select GROUP_CONCAT( tagname ) as metka from tags )as metka
FROM images
INNER JOIN images_tags ON images.id = images_tags.images_id
INNER JOIN tags ON images_tags.tags_id = tags.id
INNER JOIN category ON images.category_id = category.category_id
WHERE images.category_id = 1
GROUP BY images.name
';

Теги выводит, но, блин... в каждом вложенном массиве. То есть на выходе получаем:

[0] => Array
    (
      [name] => Иван
      [tagname] => Человек, Мужчина
      [title] => Люди
  [metka] => Человек, Мужчина, Женщина
    )
  [1] => Array
    (
      [name] => Марья
      [tagname] => Человек, Женщина
      [title] => Люди
  [metka] => Человек, Мужчина, Женщина
    )

UPD А, не, бредовый у меня скрипт получился. Он выводит вообще ВСЕ теги, без выборки по категории.

[Danil Sapegin]

На больших таблицах всё это будет ощутимо тормозить на этапе GROUP BY images.name

Мне кажется лучше отказаться вообще от лишнего запроса "многое-ко-многим" и сделать лучше отдельный запрос в рамках цикла, если в облако тегов необходимо категории выводить для каждой записи.

Чтобы чуть-чуть сэкономить на запросах могу предложить в методе, который вытаскивает картинки, добавить аттрибут, который будет отдельно добавлять к этому запросу недостающее облако тегов, но только там, где это нужно (наверняка не везде)

Оптимизировать это можно ещё сделав один запрос к tags выбрав вообще все данные из нее (вероятно таблица очень маленькая), запомнить его в памяти и дальше, при выводе конкретной картинки прогонять цикл с результатами из таблицы tags и сравнивать images.category_id и tags.category_id
Скорее всего со связями напутал, но думаю мысль должна быть ясна :)

[ТемноКот]

Danil Sapegin, Хм... в принципе можно без отдельного запроса к tags. Ведь выбранный по категории массив уже есть, все тэги там есть... написать простейшую функцию, которая будет дубликаты выкидывать... что-то вроде:

// $arrtags - массив из БД
// $key - ключ 
$tgs = list_tags($arrTable, 'tagname');

function list_tags($arrtags, $key) {
	foreach ($arrtags as $idx => $value){
		$arr[] = $arrtags[$idx][$key];
	};
	$arr = implode(',', $arr);
	$arr = explode(',', $arr);
	$arr = array_unique( $arr );
	return $arr;
}