在SQL中选择随机图像覆盖

Question

我想为专辑的封面选择一个随机图片。如果相册包含标记为“盖子”的图像，那么它应该只从这些图像中进行选择。如果一个子相册有“封面”标记的图像，那么它就不应该选择没有标记“覆盖”的图像。

这是一个视觉结构。

Album 1
├───Image 1
├───Album 2
│   ├───Image 2
│   ├───Image 3 (cover)
│   └───Image 4 (cover)
└───Album 3
    ├───Image 5
    └───Image 6

• 专辑1应从图像1、图像3、图像4、图像5、图像6中选择
• 专辑2应从图3、图4中选择
• 专辑3应从图5、图6中选择

这是我的桌子

表标签

| id | name  |
|----|-------|
| 1  | cover |

表image_tag

| id | image_id | tag_id |
|----|----------|--------|
| 1  | 3        | 1      |
| 2  | 4        | 1      |

表album_image

| id | album_id | image_id |
|----|----------|----------|
| 1  | 1        | 1        |
| 2  | 2        | 2        |
| 3  | 2        | 3        |
| 4  | 2        | 4        |
| 5  | 3        | 5        |
| 6  | 3        | 6        |

表相册

| id | name    | parent_id |
|----|---------|-----------|
| 1  | album 1 | null      |
| 2  | album 2 | 1,        |
| 3  | album 3 | 1,        |

这就是我提出的SQL。

SELECT ai.image_id AS image_id FROM albums AS a
INNER JOIN album_image AS ai ON ai.album_id = a.id
LEFT JOIN image_tag AS it ON it.image_id = ai.image_id AND it.tag_id = 1[1]
WHERE a.id = 1[2] OR parent_id LIKE '1,%'[3]
ORDER BY CASE
    WHEN it.tag_id IS NOT NULL AND a.id = 1[4] THEN RAND()
    ELSE RAND() + 1
END  ASC LIMIT 1;

1:这是PHP中另一个SQL当前的get。如果可以合并，那就太好了。是否可以左加入一个内部连接表？如果标签不存在怎么办？

2，4:这是来自PHP的专辑id。

3:这是来自PHP，以确保子相册包括在内。

该解决方案将生成以下结果。

• 专辑1应从图像1、图像2、图像3、图像4、图像5、图像6中选择
• 专辑2应从图3、图4中选择
• 专辑3应从图5、图6中选择

是否有一种方法可以修改SQL以便生成预期的结果？

我在运行MySQL。

编辑用于测试的模式

CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO tags (name) VALUES ('cover');

CREATE TABLE image_tag (
  id INT AUTO_INCREMENT PRIMARY KEY,
  image_id INT NOT NULL,
  tag_id INT NOT NULL
);
INSERT INTO image_tag (image_id, tag_id) VALUES (3, 1);
INSERT INTO image_tag (image_id, tag_id) VALUES (4, 1);

CREATE TABLE album_image (
  id INT AUTO_INCREMENT PRIMARY KEY,
  album_id INT NOT NULL,
  image_id INT NOT NULL
);
INSERT INTO album_image (album_id, image_id) VALUES (1, 1);
INSERT INTO album_image (album_id, image_id) VALUES (2, 2);
INSERT INTO album_image (album_id, image_id) VALUES (2, 3);
INSERT INTO album_image (album_id, image_id) VALUES (2, 4);
INSERT INTO album_image (album_id, image_id) VALUES (3, 5);
INSERT INTO album_image (album_id, image_id) VALUES (3, 6);

CREATE TABLE albums (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id TEXT
);
INSERT INTO albums (name, parent_id) VALUES ('album 1', NULL);
INSERT INTO albums (name, parent_id) VALUES ('album 2', '1,');
INSERT INTO albums (name, parent_id) VALUES ('album 3', '1,');

Answer 1

这是我的解决办法。

SELECT ai.image_id AS image_id, (
    SELECT COUNT(*) FROM albums AS a2
        INNER JOIN album_image AS ai2 ON ai2.album_id=a2.id 
        LEFT JOIN image_tag AS it2 
            INNER JOIN tags AS t2 ON t2.id = it2.tag_id 
        ON it2.image_id = ai2.image_id AND t2.name = 'cover'
        WHERE a2.id = a.id AND t2.name IS NOT NULL AND it.tag_id IS NULL
    ) AS count FROM albums AS a 
INNER JOIN album_image AS ai ON ai.album_id=a.id 
LEFT JOIN image_tag AS it 
    INNER JOIN tags AS t ON t.id = it.tag_id 
ON it.image_id = ai.image_id AND t.name = 'cover'
WHERE a.id = 1 OR parent_id LIKE '1,%' 
ORDER BY CASE 
    WHEN it.tag_id IS NOT NULL AND a.id = 1 THEN RAND() 
    WHEN count > 0 THEN RAND() + 2
    ELSE RAND() + 1 
END ASC LIMIT 1;