Thursday, 5 September 2013

UNION on 2 tables returns one almost empty result every time

UNION on 2 tables returns one almost empty result every time

I have something called Galleries that group media together. That media
can be photos or videos. I store photos in one table and videos in
another, so I am using a UNION query to find photos and videos that belong
to a gallery.
My problem seems to be that my result contains an empty object (no ID) for
one of the tables — to rephrase, it will always return a useless result
for one of the tables being queried if there are no results in that table.
First, the query:
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;
I am adding the type column so that I may identify what kind of result I
get back. I have nulled the results that are not common between the
tables.
Like I said, it works, but not quite as expected. If I have a gallery that
contains only photos, I still get an (almost) empty video result. An
example result:
[] => 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
)
The first result, labelled with [type]=>video is empty, I call it, because
it does not have an ID of a video, title, embed code, etc... It only
contains the gallery_id and type.
This was the most complicated query I have put together so far, and I am
sure there is something that I am missing. If a gallery only contains
videos or only contains photos, I'd like the results to reflect that.
To be a hack, I could check to see if there is an ID when I foreach on
these results before I echo something, but I know that my query could be
improved instead. Help?

No comments:

Post a Comment