Laravel/SQL returning data from many-to-many relationship as array of data

Question

I have the following setup: a posts table, a post_pics table, and a post_post_pictures table which acts as a bridging table between posts and post_pics. One post can have multiple photos. I'd like to select all of the images belonging to that post and return them as an array for each post (example data at the bottom of the question).

The setup for the tables is the following:

posts:

+--------------+-----------------+------+-----+---------+-------+
| Field        | Type            | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+-------+
| id           | char(36)        | NO   | PRI | NULL    |       |
| ............... Other fields not important .................. |
|                                                               |
+--------------+-----------------+------+-----+---------+-------+

post_pics:

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | char(36)     | NO   | PRI | NULL    |       |
| picture | varchar(255) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

post_post_pictures:

+-----------------+----------+------+-----+---------+-------+
| Field           | Type     | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| post_id         | char(36) | NO   | MUL | NULL    |       |
| post_picture_id | char(36) | NO   | MUL | NULL    |       |
+-----------------+----------+------+-----+---------+-------+

I have the following query where I'm selecting posts in a given range based on some coordinates (that part works fine) - I'm having trouble selecting all photos that each post has, and having those photos be returned in one "post" record.

The joins I do on post_post_pictures and post_pics are wrong/incomplete I think.

$meanRadiusOfEarthInMiles = 3959;
$poiSearchDistanceInMiles = 60;
$clientLatitude = floatval($request->get('latitude'));
$clientLongitude = floatval($request->get('longitude'));
$posts = Post::select(
    'posts.*',
    'users.id as user_id',
    'users.photo as company_photo',
    'users.name as company_name',
    'post_pics.picture as images'
    )
    ->join('users', 'posts.user_id', '=', 'users.id')                // This is incomplete I think
    ->join('post_post_pictures', 'posts.id','=', 'post_id')          // and this one, I think
    ->join('post_pics', 'post_post_pictures.post_id','=', 'post_id')
    ->where('active', '=', '1')
    ->where('account_type', '=', 'business')
    ->selectRaw("($meanRadiusOfEarthInMiles *
                    acos(cos(radians(?)) * cos(radians(latitude)) *
                    cos(radians(longitude) - radians(?)) +
                    sin(radians(?)) * sin(radians(latitude)))
            ) AS miles_away", [$clientLatitude, $clientLongitude, $clientLatitude])
            ->havingRaw("miles_away < ?", [$poiSearchDistanceInMiles])
            ->get();

The above query returns almost the right data, but it looks like it returns one row for each image, instead of adding all the images belonging to a post to that record.

For example, right now I'm getting lots of records like this:

{
"id": "0792c20e-498c-4aa7-a2b2-f06f4775dc82",
"title": "test",
"description": "test",
"location": "<location name>",
"latitude": "<some lat>",
"longitude": "<some lon>",
"user_id": "271564de-35fa-4023-9b13-f68d0e9100d0",
"images": "<URL>/<IMAGE 1>",
"miles_away": 5.313459784530729
},

and I'm hoping to be able to return rows like this:

{
"id": "0792c20e-498c-4aa7-a2b2-f06f4775dc82",
"title": "test",
"description": "test",
"location": "<location name>",
"latitude": "<some lat>",
"longitude": "<some lon>",
"user_id": "271564de-35fa-4023-9b13-f68d0e9100d0",
"images": ["<URL>/<IMAGE 1>", "<URL>/<IMAGE 2>", ...],  // return all images for the post as array would be ideal
"miles_away": 5.313459784530729
},

Is this possible? I want to return it like this since it's easy for the client application to work with.

Answer 1

Thank you @Nick Maroulis for the comment that got me on the right track to solve this.

My query ended up looking like this:

$posts = Post::select(
    'posts.*',
    'users.id as user_id',
    'users.photo as company_photo',
    'users.name as company_name',
    DB::raw('GROUP_CONCAT(post_pics.picture) as images')  // < ---- change
)
    ->join('users', 'posts.user_id', '=', 'users.id')                
    ->join('post_post_pictures', 'posts.id','=', 'post_id')          
    ->join('post_pics', 'post_post_pictures.post_picture_id','=', 'post_pics.id')
    ->where('active', '=', '1')
    ->where('account_type', '=', 'business')
    ->selectRaw("($meanRadiusOfEarthInMiles *
                    acos(cos(radians(?)) * cos(radians(latitude)) *
                    cos(radians(longitude) - radians(?)) +
                    sin(radians(?)) * sin(radians(latitude)))
            ) AS miles_away", [$clientLatitude, $clientLongitude, $clientLatitude])
            ->havingRaw("miles_away < ?", [$poiSearchDistanceInMiles])
            ->groupBy('post_post_pictures.post_id') // < ---- change
            ->get();

Which gives output similar to the following:

{
"id": "c4df92c6-1abf-4aeb-974c-b06b75dd45d2",
"title": "test post",
"latitude": "...",
"longitude": "...",
"availability": "...",
....
"user_id": "62fa9a40-3bea-48e5-badb-87d2c8d2ce35",
"views": 1,
"images": "<URL 1>,<URL 2>",                        // comma seperated URLs for the images
"miles_away": 0.5339829289762683
}

The images end up being comma separated instead of in an array when I send it back to the client in JSON format, but that's fine :)