How can I modify this MySQL query to get a particular data structure?

Question

If I have two simple tables:

Photos :

photoid | filename            | albumid
1       | something.jpg       |    1
2       | somethingelse.jpg   |    1

And an Albums table :

  albumid   | album_name        | owner_id
    1       | My Holiday 2012   |    1
    2       | My Holiday 2013   |    6

And I want to get all photos contained within all albums for a given owner_id

I can do the following :

select albums.*, photos.*
from photo_albums as albums
left join photos as photos
on albums.albumid = photos.albumid
where albums.owner_id = :owner_id

This will return 2 rows, on for each photo as well as the relevant album data.

However what I would like to do is return only on row per album. With the photos rows nested within each album row.

Returning output similar to the following:

[{
    albumid : 1,
    album_name : My Holiday 2012,
    owner_id : 1,
    photos : {
                 photoid : 1,
                 filename : something.jpg
             },
             {
                 photoid : 2,
                 filename : somethingelse.jpg
             }   
}]

Is there an easy and efficient way to do this within the MySQL query itself? If so how? Or alternatively am I better taking the complete row data from the original query and constructing the data into this output by looping through the rows in php?

Thanks in advance

Answer 1

You just can't do it by modifying SQL query.

I see two ways to achieve your goal:

1. Postprocess your output to build needed array structure.
2. Use a ORM (Object-relational mapping) to automatically map your query results to objects. For example, see Doctrine.