How to display a lot of attachments along with posts?

Question

MySQL

Hello! Please help me figure out if I made a mistake when designing, or even so, this is a common structure.
The site has a feed of posts, a post can contain 3 types of attachments (picture, video, gif animation).
the structure of the database is as follows:
posts - post table
images - image table
videos - video table
gifi - gif table
When getting posts from the database, I used something like this query.
I get a list of posts and in subqueries, using a separator, I separate the fields I need, which I parse on the php side and collect the attachment object from them. Why not LEFT JOIN: "because a post can contain more than one attachment of each type"
And this is where my doubts crept in... What if I had more attachments supported. The request does not look aesthetically pleasing. And given that video and, moreover, GIF are contained in 5 percent of posts, then 95% of requests contain extra subqueries to get video and GIF from the database.
Tell me, is this a normal practice with such a bunch of subrequests, or does it all need to be collected on the php side by requesting each attachment option individually based on previously received posts?

SELECT 
`posts`.*, 
(SELECT GROUP_CONCAT(CONCAT(`images`.`id`,':',`images`.`name`) SEPARATOR ',') FROM `images` WHERE `images`.`post_id` = `posts`.`id`) images
(SELECT GROUP_CONCAT(CONCAT(`videos`.`id`,':',`videos`.`name`) SEPARATOR ',') FROM `videos` WHERE `videos`.`post_id` = `posts`.`id`) videos
(SELECT GROUP_CONCAT(CONCAT(`gifi`.`id`,':',`gifi`.`name`) SEPARATOR ',') FROM `gifi` WHERE `gifi`.`post_id` = `posts`.`id`) gifi
 
FROM `posts`
LEFT JOIN `` ON

Answer 1

I usually rake out all the nesting with one footcloth (we achieve uniqueness of field names by adding the necessary prefixes):

Select p.id as post_id, p.create_date as p_create_date, p.title as p_title, p.text as p_text,
           img.id as img_id, img.name as img_name,
          v.id as v_id, v.name as v_name
from posts p
left join images img on img.post_id = p.id
left join videos v on v.post_id = p.id

And then in PHP I collect an associative array with the desired nesting depth:

$out = [];
foreach($rows as $row)
{
  $post = &$out[$row['POST_ID']]; //ссылка на элемент массива первого уровня - пост
  $post['CREATE_DATE'] = $row['P_CREATE_DATE'];
  $post['TITLE'] = $row['P_TITLE'];
  $post['TEXT'] = $row['P_TEXT'];
   if(!is_null($row['IMG_ID']))  
  {
      $image = &$post['IMAGES'][$row['IMG_ID']]; //ссылка на элемент массива второго уровня - элемент массива изображений
      $image['NAME'] = $row['IMG_NAME'];
  }
   if(!is_null($row['V_ID']))  
  {
     $video = &$post['VIDEOS'][$row['V_ID']]; //ссылка на элемент массива второго уровня - элемент массива видео
      $video['NAME'] = $row['V_NAME'];
  }
}

At the output, we get $out - a completely structured object suitable for further processing and delivery to the frontend.
PS: Using references speeds up the assembly of such structures.
For example, to fill several properties of an IMAGES array element that is nested, the construction will work out faster:

$post = &$out[$row['POST_ID']];
$image = &$post['IMAGES'][$row['IMG_ID']];
$image['NAME'] = $row['IMG_NAME'];
$image['WIDTH'] = $row['IMG_WIDTH'];
$image['HEIGHT'] = $row['IMG_HEIGHT'];

Than such a heap to fill each property:

$out[$row['POST_ID']]['IMAGES'][$row['IMG_ID']]['NAME'] = $row['IMG_NAME'];
$out[$row['POST_ID']]['IMAGES'][$row['IMG_ID']]['WIDTH'] = $row['IMG_WIDTH'];
$out[$row['POST_ID']]['IMAGES'][$row['IMG_ID']]['HEIGHT'] = $row['IMG_HEIGHT'];

Answer 2

3 types of attachments (picture, video, gif animation).
- note that your given attachments are subtypes of attachments. If they are stored on your server, they are just files.
perhaps there is an option to think about posts->files , and already segment inside. then you will get rid of two requests.
- also note - do you use nested files anywhere else? if not, you might be comfortable with a JSON field in Mysql. then you won't need to do additional joins.