Mysql Query Group Concat Limit multiple record of child table in single query

Question

Album Table:

+------------------------------+
| id      name       updated_at|
+------------------------------+

Album_member Table:

+------------------------------------+
| id      album_id         member_id |
+------------------------------------+

Album_media Table:

+--------------------------------+
| id       album_id         link |
+--------------------------------+

My Query:

$limit = 30;
$member_id = 1;
SELECT a . *,
(SELECT GROUP_CONCAT(CONCAT('$assets/album_image/medium/', ds_album_media.link) SEPARATOR ',') as link
FROM ds_album_media WHERE ds_album_media.album_id = a.id ORDER BY a.updated_at desc LIMIT 0 , 5)
AS photo_link FROM `ds_album` a
LEFT JOIN ds_album_media amd ON amd.album_id = a.id
LEFT JOIN ds_album_member amb ON amb.album_id = a.id
where amb.member_id = " . $member_id . " GROUP BY a.id
ORDER BY a.updated_at desc LIMIT 0 , $limit

Now for each album there is multiple album_media and album_member is for which album user is following i want data in such a way that retrieve all album that use follows and all photos of that album but constrains is that need to set album_media limit 5 and album limit 30.

limit not working as i have used group_concat any other way to limit the records of group_concat?
I am using laravel so any solution regarding that is also most welcome.

I have used substring_index() function but is it sufficient way in perspective of performance?

I have tried this query with laravel,

Laravel Query(Edit):

$data = Album::select("album.*")
    ->leftjoin("album_media", 'album_media.album_id', '=', 'album.id')
                    ->leftjoin("album_member", 'album_member.album_id', '=', 'album.id')
    ->where('album_member.member_id', $member_id)
    ->with(['albumMedia' => function($query) {
       return $query->where('album_id','album.id')->take(5);
    }])
    ->groupBy('album.id')->orderBy('album.updated_at', 'desc')->take($limit)->skip($skip)->get()->toArray();

i wonder if you had tried [eager loading constraint](http://laravel.com/docs/5.1/eloquent-relationships#constraining-eager-loads). however, this eloquent approach will have a nesting structure. — Bagus Tesa, Dec 04 '15 at 09:16
@Tezla that i have tried but i have dynamic id so it's not working just look at it...`$data['album'] = Album::select("album.*")->leftjoin("album_media", 'album_media.album_id', '=', 'album.id')->leftjoin("album_member", 'album_member.album_id', '=', 'album.id')->where('album_member.member_id', $member_id)->with(['albumMedia' => function($query) { return $query->where('album_id','album.id')->take(5); }])->groupBy('album.id')->orderBy('album.updated_at', 'desc')->take($limit)->skip($skip)->get()->toArray();` — Sagar Naliyapara, Dec 04 '15 at 09:23
laravel *raw* queries are rather beyond me, if you wish to retrieve any album with it's relation (album_media and album_member). it can be done through `Album::where('id', $id)->with(['albumMedia' => function($query){return $query->take(5);}, 'albumMember' => function($query) use($member_id){return $query->where('member_id', $member_id')->take(30);}])->first();` assuming you had proper relations among model.. ps. i think you need to pass `$member_id` to eager constraint. — Bagus Tesa, Dec 04 '15 at 09:31
Thanks for the reply @Tezla but album id is not static album_id are those returned by `AlbumMember` so it stored in album_media with appropriate `$member_id` i have only `member_id` thank you very much for your complimate — Sagar Naliyapara, Dec 04 '15 at 09:46
this may help its a way to limit a group_concat statement http://stackoverflow.com/questions/23608464/group-concat-with-limit — vik, Dec 04 '15 at 09:51
Thanks @vik this is currently my temporary solution but it is not better way i think because it will retrieve first all data then it will avoid rest data — Sagar Naliyapara, Dec 04 '15 at 09:52
oh, glad you had it working. anyway, if you need to pass another variable to an eager load. use php [`use(..)`](https://bryce.fisher-fleig.org/blog/php-what-does-function-use-syntax-mean/) to pass extra parameters to the eager load closure. — Bagus Tesa, Dec 04 '15 at 09:55
@Tezla that's what i was trying but how can i pass it as i have dynamic `album.id` in query just check my Edit content in question By the way thanks a lot for your time — Sagar Naliyapara, Dec 04 '15 at 10:00
@Tezla in Edit part i have shown where condition but it can not find album.id — Sagar Naliyapara, Dec 04 '15 at 10:03
see the second answer here http://stackoverflow.com/questions/1522509/how-to-hack-mysql-group-concat-to-fetch-a-limited-number-of-rows may be help somehow — vik, Dec 04 '15 at 10:04
you already have a WHERE clause so using the solution linked above with SUBSTRING_INDEX should not cause much overhead. — Mihai, Dec 04 '15 at 10:07
@Mihai you are talking about which `WHERE` clause...?? Is my query will only retrieve only 5 `album_media` or will retrive all data and then cut of the rest data...?? — Sagar Naliyapara, Dec 04 '15 at 10:22
All the data filtered by WHERE,I`m guessing is not that much waste if it gets lets say 50 rows but in GROUP CONCAT it only needs 5 — Mihai, Dec 04 '15 at 10:24
@Mihai If i have more than thousand record than it will retrieve first and then apply this function so may be not optimized way is it...?? — Sagar Naliyapara, Dec 04 '15 at 10:25
@vik still it will not work for me Thank you very much for suggestion but i have already tried it not working because of need different limit for two different records... — Sagar Naliyapara, Dec 04 '15 at 10:32

score 1 · Accepted Answer · answered Feb 22 '16 at 19:48

Set up your relationships and then constrain them like so:

$albums = Album::with('albumMedia', 'albumMembers')
->whereHas('albumMembers', function($query) use ($userId) {
    $query->where('albumMembers.member_id', $userId);
})
->orderBy('album.updated_at')
->take($limit)
->skip($skip)
->get();

Lastly, you could then add a method to the Album model which will help you retrieve your concatenated string/link, based on the eagerly-loaded relationship.

Better yet, instead of doing a whereHas, you could so something like a scope method on the Album model which will create it for you. This would be much cleaner than the example above.