我有一个叫做画廊的东西,它将媒体组合在一起。该媒体可以是照片或视频。我将照片存储在一个表中,将视频存储在另一个表中,因此我使用 UNION 查询来查找属于图库的照片和视频。
我的问题似乎是我的结果包含其中一个表的空对象(没有 ID)——改写一下,如果该表中没有结果,它将始终为正在查询的其中一个表返回无用的结果。
首先,查询:
SELECT * from (
SELECT g.id AS gallery_id, 'photo' AS type, p.id AS id, p.filename, p.caption, null AS title, null AS service, null AS embed, null AS width, null AS height, p.display_order FROM galleries g
LEFT OUTER JOIN photos AS p ON p.gallery_id = g.id
WHERE g.id = {$this->id}
UNION
SELECT g.id AS gallery_id, 'video' AS type, v.id AS id, null AS filename, null AS caption, v.title, v.service, v.embed, v.width, v.height, v.display_order FROM galleries g
LEFT OUTER JOIN videos AS v ON v.gallery_id = g.id
WHERE g.id = {$this->id}
) AS u ORDER BY display_order;
我正在添加type
列,以便我可以确定我得到的结果类型。我已经取消了表之间不常见的结果。
就像我说的,它有效,但与预期不尽如人意。如果我有一个只包含照片的画廊,我仍然得到一个(几乎)空的视频结果。示例结果:
[] => Galleries Object
(
[id] =>
[name] =>
[slug] =>
[gallery_id] => 32
[type] => video
[filename] =>
[caption] =>
[title] =>
[service] =>
[embed] =>
[width] =>
[height] =>
[display_order] =>
)
[39] => Galleries Object
(
[id] => 39
[name] =>
[slug] =>
[gallery_id] => 32
[type] => photo
[filename] => 39-studio-blue-pacific.jpg
[caption] =>
[title] =>
[service] =>
[embed] =>
[width] =>
[height] =>
[display_order] => 1
)
第一个结果,标有 [type]=>video
是空的,我称之为,因为它没有视频、标题、嵌入代码等的 ID......它只包含gallery_id
和type
。
这是我迄今为止整理的最复杂的查询,我确信我缺少一些东西。如果图库只包含视频或仅包含照片,我希望结果能够反映这一点。
作为一个黑客,我可以在回应某些内容之前检查一下这些结果是否有ID
,但我知道我的查询可以改进。帮助?
您返回这些结果的原因是您在表上使用外部联接。试试这个:
SELECT * from (
SELECT g.id AS gallery_id, 'photo' AS type, p.id AS id, p.filename, p.caption, null AS title, null AS service, null AS embed, null AS width, null AS height, p.display_order FROM galleries g
JOIN photos AS p ON p.gallery_id = g.id
WHERE g.id = {$this->id}
UNION
SELECT g.id AS gallery_id, 'video' AS type, v.id AS id, null AS filename, null AS caption, v.title, v.service, v.embed, v.width, v.height, v.display_order FROM galleries g
JOIN videos AS v ON v.gallery_id = g.id
WHERE g.id = {$this->id}
) AS u ORDER BY display_order;
另外,听起来你可能会从阅读我写的这篇关于SQL的文章中获得很多好处,以帮助你更好地理解这些概念。