title: Mysql Query Group Concat Limit multiple record of child table in single query
body: <h3><strong>Album Table:</strong></h3>
  
  <pre><code>+------------------------------+
  | id      name       updated_at|
  +------------------------------+
  </code></pre>
  
  <h3><strong>Album_member Table:</strong></h3>
  
  <pre><code>+------------------------------------+
  | id      album_id         member_id |
  +------------------------------------+
  </code></pre>
  
  <h3><strong>Album_media Table:</strong></h3>
  
  <pre><code>+--------------------------------+
  | id       album_id         link |
  +--------------------------------+
  </code></pre>
  
  <h3>My Query:</h3>
  
  <pre><code>$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
  </code></pre>
  
  <p>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 <strong><em>constrains is that need to set album_media limit 5 and album limit 30.</em></strong></p>
  
  <ol>
  <li><p><code>limit</code> not working as i have used group_concat any other way to limit the records of group_concat?</p></li>
  <li><p>I am using laravel so any solution regarding that is also most welcome.</p></li>
  </ol>
  
  <p><strong><em>I have used substring_index() function but is it sufficient way in perspective of performance?</em></strong></p>
  
  <p>I have tried this query with laravel,</p>
  
  <h3><strong>Laravel Query(Edit):</strong></h3>
  
  <pre><code>$data = Album::select("album.*")
      -&gt;leftjoin("album_media", 'album_media.album_id', '=', 'album.id')
                      -&gt;leftjoin("album_member", 'album_member.album_id', '=', 'album.id')
      -&gt;where('album_member.member_id', $member_id)
      -&gt;with(['albumMedia' =&gt; function($query) {
         return $query-&gt;where('album_id','album.id')-&gt;take(5);
      }])
      -&gt;groupBy('album.id')-&gt;orderBy('album.updated_at', 'desc')-&gt;take($limit)-&gt;skip($skip)-&gt;get()-&gt;toArray();
  </code></pre>