我有这个表page_base
我只想从另一个名为 page_base_images
ON page_base.id = page_base_images.page_base_id
的表中position = 1
连接一个图像。
page_base [表]
+----+---------+------------+
| id | menu_id | submenu_id |
+----+---------+------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
+----+---------+------------+
page_base_images [表]
+----+--------------+------------+----------+--------+
| id | page_base_id | image | position | active |
+----+--------------+------------+----------+--------+
| 1 | 1 | 01_001.jpg | 1 | 1 |
| 2 | 1 | 01_002.jpg | 2 | 1 |
| 3 | 1 | 01_003.jpg | 3 | 1 |
| 4 | 1 | 01_004.jpg | 4 | 1 |
| 5 | 1 | 01_005.jpg | 5 | 1 |
| 6 | 1 | 01_006.jpg | 6 | 1 |
| 7 | 1 | 01_007.jpg | 7 | 1 |
| 8 | 1 | 01_008.jpg | 8 | 1 |
| 9 | 1 | 01_009.jpg | 9 | 1 |
| 10 | 1 | 01_010.jpg | 10 | 1 |
| 11 | 3 | 03_001.jpg | 1 | 1 |
| 12 | 3 | 03_002.jpg | 2 | 1 |
| 13 | 3 | 03_003.jpg | 3 | 1 |
+----+--------------+------------+----------+--------+
所需的阵列结果
+----------+--------------+----------+----------+-------------+------------+
| array_id | page_base_id | title | subtitle | description | image |
+----------+--------------+----------+----------+-------------+------------+
| 0 | 1 | *string* | *string* | *string* | 01_001.jpg |
| 1 | 3 | *string* | *string* | *string* | 03_001.jpg |
+----------+--------------+----------+----------+-------------+------------+
到目前为止,我有这个,但它总是添加带有id = 1
的图像,如果我放置一个WHERE
子句,它会给我错误。
$page_base_table = get_raw_query("
SELECT
base_lang.title, base_lang.subtitle, base_lang.description, base_image.image
FROM page_base base
LEFT OUTER JOIN page_base_languages base_lang ON base.id = base_lang.page_base_id
LEFT OUTER JOIN
(SELECT image.page_base_id, image.image FROM page_base_images image LIMIT 1)
base_image ON base.id = base_image.page_base_id
WHERE base_lang.title LIKE '%$string%' ");
有人可以帮助我将page_base_images连接限制为 1 并用 position = 1
抓取图像吗?
您可以通过在page_base_images
的连接中添加附加条件来重写查询,即ON (base.id = base_image.page_base_id AND base_image.position =1)
SELECT
base_lang.title,
base_lang.subtitle,
base_lang.description,
base_image.image
FROM
page_base base
LEFT OUTER JOIN page_base_languages base_lang
ON base.id = base_lang.page_base_id
LEFT OUTER JOIN page_base_images AS base_image
ON (base.id = base_image.page_base_id AND base_image.position =1)
WHERE base_lang.title LIKE '%$string%'
编辑以获取具有最小位置的图像
SELECT
base_lang.title,
base_lang.subtitle,
base_lang.description,
base_image.image
FROM
page_base base
LEFT OUTER JOIN page_base_languages base_lang
ON base.id = base_lang.page_base_id
LEFT OUTER JOIN
(SELECT page_base_id,image,MIN(`position`) FROM page_base_images GROUP BY page_base_id)
AS base_image
ON (base.id = base_image.page_base_id)
WHERE base_lang.title LIKE '%$string%
删除子查询并尝试此操作:
SELECT
base_lang.title,
base_lang.subtitle,
base_lang.description,
base_image.image
FROM page_base base
LEFT OUTER JOIN page_base_languages base_lang
ON base.id = base_lang.page_base_id
LEFT OUTER JOIN page_base_images
ON base.id = page_base_images.page_base_id
AND page_base_images.position = 1
WHERE base_lang.title LIKE '%$string%'
我了解,您的page_base_images表中永远不会有两个位置= 1的图像。你有没有尝试过这样的事情:
$page_base_table = get_raw_query("SELECT base_lang.title, base_lang.subtitle, base_lang.description, base_image.image FROM page_base base LEFT OUTER JOIN page_base_languages base_lang ON base.id = base_lang.page_base_id LEFT OUTER JOIN page_base_images image AS base_image ON base.id = base_image.page_base_id AND base_image.position=1 WHERE base_lang.title LIKE '%$string%' ");
从page_base_images开始选择,然后根据结果左联接语言和左联接,并在 page_base_images.page_id 列上显示不同的列。
这将为您提供所需的内容,并且听起来比其他解决方案更合乎逻辑和错误安全。
假设image_position总是唯一的,并且image_position 1 存在在我看来不够安全。
SELECT
base_lang.title,
base_lang.subtitle,
base_lang.description,
base_image.image,
DISTINCT(base.id) as baseId
FROM
page_base_images base_image
LEFT OUTER JOIN page_base base
ON (base.id = base_image.page_base_id)
LEFT OUTER JOIN page_base_languages base_lang
ON base.id = base_lang.page_base_id
WHERE base_lang.title LIKE '%$string%'