Как вывести много вложений вместе с постами?

Question

[Алексей Коновалов]

Всем привет! Помогите пожалуйста разобраться ошибся ли я при проектировании или всеже это обычная структура.
На сайте имеется лента постов, пост может содержать 3 типа вложений (картинка, видео, gif анимация).
структура БД такая:
posts - таблица постов
images - таблица изображений
videos - таблица видео
gifi - таблица gif
При получении постов из БД я использовал примерно такой запрос.
Я получаю список постов и во вложенных запросах с помощью сепаратора отделяю нужные мне поля, которые на стороне php разбираю и собираю из них объект вложения. Почему не LEFT JOIN: "потому что пост может содержать более одного вложения каждого типа"
И вот тут у меня закрались сомнения... Что если бы у меня поддерживалось больше вложений. Запрос и так выглядит не эстетично. А учитывая то, что видео и, тем более, GIF содержатся в процентов 5 постов, то 95% запросов содержат лишние подзапросы для получения видео и GIF из БД.
Подскажите, нормальная ли это практика с такой кучей подзапросов или это все нужно собирать на стороне php запрашивая по отдельности каждый вариант вложения на основе предварительно полученных постов?

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

[alexalexes]

Я обычно выгребаю всю вложенность одной портянкой (добиваемся уникальности имен полей, добавляя необходимые префиксы):

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

А потом в PHP собираю ассоциативный массив с нужной глубиной вложенности:

$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'];
  }
}

На выходе получаем $out - вполне себе структуированный объект, пригодный для дальнейшей обработки и выдачи на фронтенд.
PS: Использование ссылок ускоряет сборку таких структур.
Например, чтобы заполнить несколько свойств элемента массива IMAGES, который вложен, быстрее будет отрабатывать конструкция:

$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'];

Чем вот такое нагромождение для заполнения каждого свойства:

$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'];

Comments

[Алексей Коновалов]

А как будут вести себя такие запросы, если в БД будет, скажем, несколько десятков тысяч постов, в таблице видео, скажем, чуть меньше чем постов, а в таблице изображений несколько миллионов записей с изображениями? Есть ли предел при котором такая структура положит сервер?

[alexalexes]

Если у вас очень большая выборка получается, то получайте из базы и отдавайте клиенту информацию порциями.
Например, от известного поста start_post_id (начало будет -1) + определенное количество постов post_count.

Select *
  from (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,
               @p_count := if(@post_id != p.id, @post_id + 1, @post_id) as p_count,
               @post_id := p.id
          from posts p
               left join images img on img.post_id = p.id
               left join videos v on v.post_id = p.id,
               (SELECT @p_count := 0, @post_id := -1) r
         where p.id > :start_post_id
           and @p_count <= :post_count
     order by p.create_date 
       ) a
where a.p_count < :post_count

[Алексей Коновалов]

alexalexes, Спасибо! А подскажит еще что это за записи такие? Как их погуглить?

@p_count := if(@post_id != p.id, @post_id + 1, @post_id) as p_count,
               @post_id := p.id

Не понятны вот эти:

@p_count :=
@post_id :=

[alexalexes]

Это переменные:
@post_id - в нее фиксируется предыдущее значение post_id;
@p_count - а сюда фиксируется порядковый номер выбранного поста для этой выборки (это не идентификатор, а просто порядковый номер).
Тут задаются начальные значения:
(SELECT @p_count := 0, @post_id := -1) r
А тут - переприсваиваются значения при выборке каждой строки:

@p_count := if(@post_id != p.id, @post_id + 1, @post_id) as p_count,
 @post_id := p.id

Поведение как в процедурном программировании.
Считай, что select - это оператор foreach.
А перечисление атрибутов select-а - это тело цикла.
Порядок атрибутов тут важен, как и порядок строк внутри обычного цикла foreach.
PS: Следует обратить внимание на то, если нужно каким-то образом пронумеровать порядок выборки, то следует указывать order by - как сортировать выборку. Иначе получите непредсказуемый результат нумерации.

Answer 2

[Dmitry Bay]

3 типа вложений (картинка, видео, gif анимация).
- заметьте, что у вас данные вложения являются подтипами вложения. Если они хранятся у вас на сервере, то представляют собой просто файлы.
возможно есть вариант подумать о posts->files , а уже внутри сегментировать. тогда от двух запросов вы избавитесь.

- еще заметьте - используете ли вы где либо вложенные файлы еще? если нет, то возможно вам будет удобно поле JSON в Mysql. тогда вам не нужно будет делать дополнительные join.