SQL-запрос на основе тегов

Question

Прошло много времени с тех пор, как я создал SQL, и я не уверен, есть ли у этой проблемы простое решение или нет. Также я немного нуб.

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

Это упрощенная версия моей текущей структуры базы данных:

(2 таблицы с дополнительной таблицей ссылок "многие ко многим")

CREATE TABLE images(
   image_id INT(12) AUTO_INCREMENT,
   image_name VARCHAR(128),
   PRIMARY KEY(image_id)
)ENGINE= INNODB;

CREATE TABLE tags(
   tag_name VARCHAR(64) NOT NULL,
   PRIMARY KEY(tag_name)
)ENGINE= INNODB;

CREATE TABLE images_tags_link(
   image_id_fk INT(12),
   tag_name_fk VARCHAR(64) NOT NULL,
   PRIMARY KEY(image_id_fk,tag_name_fk),
   FOREIGN KEY(image_id_fk) REFERENCES images(image_id),
   FOREIGN KEY(tag_name_fk) REFERENCES tags(tag_name)
)ENGINE= INNODB;

Пример данных:

 ===images===
 ___________________________       
| image_id |  image_name    |     
|----------|----------------|     
|     1    |  image_001.jpg |     
|     2    |  image_002.jpg |     
|     3    |  image_003.png |     
|     4    |  image_004.jpg |     
|     5    |  image_005.gif |     
 ---------------------------                                     
 ===tags===                                 
 _______________
|    tag_name   |
|---------------|
| Landscape     |
| Portrait      |
| Illustration  |
| Photo         |
| Red           |
| Blue          |
| Character     |
| Structure     |
 ---------------
===images_tags_link===
 ________________________________
| image_id_fk | tag_name_ fk     |
|-------------|------------------|
|      1      |    Landscape     |
|      1      |    Illustration  |
|      1      |    Blue          |
|      2      |    Blue          |
|      2      |    structure     |
|      2      |    Landscape     |
|      3      |    Illustration  |
|      4      |    Red           |
|      4      |    Portrait      |
|      4      |    Character     |
|      5      |    Blue          |
|      5      |    Photo         |
 --------------------------------

Моя проблема со следующим запросом:

Я ищу один запрос, который может выбрать все 'image_names' из таблицы IMAGES, в которой есть все пользователи, перечисленные в тегах, например, пользователь может искать теги 'Blue' и 'Landscape', которые должны выводить только image_names 'image_001.jpg'И' image_002.jpg '.

=== ВХОД ===

Пользователи выбрали теги: ("Синий", "Пейзаж")

=== ВЫВОД ===

Имена изображений, которые имеют ВСЕ перечисленные теги: ( 'image_001.jpg', 'image_002.jpg')

Заранее спасибо.

Answer 1

2 простых способа сделать это.

Любое из следующего, в зависимости от требуемых столбцов, количества возможных тегов и т. Д.

SELECT *
FROM images
INNER JOIN images_tags_link a ON images.image_id = a.image_id_fk AND a.tag_name_fk = 'Blue'
INNER JOIN images_tags_link b ON images.image_id = b.image_id_fk AND b.tag_name_fk = 'Landscape'


SELECT images.image_id, images.image_name, COUNT(*) AS tag_count
FROM images
INNER JOIN images_tags_link a ON images.image_id = a.image_id_fk 
WHERE a.tag_name_fk IN ('Blue', 'Landscape')
GROUP BY images.image_id, images.image_name
HAVING tag_count = 2

Answer 2

Это то, что они называли Отделом отношений, и вот один из способов сделать это:

SELECT i.*
FROM Images i 
INNER JOIN
(
   SELECT image_id_fk
   FROM images_tags_link
   WHERE tag_name_fk IN ('Blue' , 'Landscape')
   GROUP BY image_id_fk 
   HAVING COUNT(DISTINCT tag_name_fk) = 2
) t ON i.image_id = t.image_id_fk;

SQL Fiddle Demo

Это даст вам:

| IMAGE_ID |    IMAGE_NAME |
----------------------------
|        1 | image_001.jpg |
|        2 | image_002.jpg |

Идея этого запроса:

   GROUP BY image_id_fk 
   HAVING COUNT(DISTINCT tag_name_fk) = 2

в подзапросе, который будет гарантировать, что любое изображение имеет оба тега, если оно имеет только один, COUNT будет 0 и, следовательно, он будет устранен.

Answer 3

Вот еще один пример использования EXISTS:

SELECT *
FROM Images I
WHERE EXISTS (
   SELECT image_id_fk
   FROM Images_Tags_Link I2 
   WHERE I2.image_id_fk = I.Image_Id
      AND tag_name_fk IN ('Blue' , 'Landscape')
   GROUP BY I2.image_id_fk
   HAVING COUNT(DISTINCT I2.tag_name_fk) > 1)

И один, использующий IN:

SELECT *
FROM Images I
WHERE Image_Id IN (
   SELECT image_id_fk
   FROM Images_Tags_Link I2 
   WHERE I2.image_id_fk = I.Image_Id
      AND tag_name_fk IN ('Blue' , 'Landscape')
   GROUP BY I2.image_id_fk
   HAVING COUNT(DISTINCT I2.tag_name_fk) > 1)

Удачи.

Answer 4

Попробуйте этот более простой способ сделать это

---

Выбрать
имя_образа
от
images_tags_link
где tag_name_fk in ('Blue','Landscape')