Задать вопрос
@excalibur
php

Стена соцсети: выборка данных

Речь пойдет о стене соцсети, к примеру, как на вконтакте или фэйсбуке, т.е. запись на стене может содержать в себе разный тип контента: новость, запись в блог, несколько изображений, опрос и т.д.

Я пока остановился на следующей структуре базы данных:

Таблица «wall»:

id | description
1 | «Это описание первой записи на стене»
2 | «А это уже вторая запись на стене»

Таблица «wall_element»:

id | wall_id | component | element | element_id
1 | 1 | image | item | 1
2 | 1 | image | item | 2
3 | 1 | image | item | 3
4 | 2 | catalog | item | 1
5 | 2 | catalog | item | 2
6 | 2 | catalog | shop | 1
7 | 2 | catalog | shop | 2

Примеры записей как и примеры структур таблиц абстрактные. Думаю с таблицей «wall» все понятно. Таблица «wall_element» содержит список прикрепленных разных типов контента, которые добавлены в запись на стену. Поля в таблице означают следующее:

wall_id — id записи на стене,
component — название компоненты. Компоненты у меня — это, к примеру, каталог, изображение, новость, блог…
element — это структурная единица компоненты, например, в компоненте «catalog» могут быть несколько таких структурных единиц, например «item» или «product» — это сам товар, «shop» — это магазин, «catagory» — категория товара и т.д. Тоже с компонетой «image», которая отвечает за загрузку и отображение фото.
element_id — это id структурной единицы комопненты, т.е. id товара, магазина, фото, записи в блог, новости и т.д. Струткрутрные единицы компонеты также имеют свои таблицы, например, catalog_product, catalog_shop, image_item, blog_item, news_item и т.д. в которых и находится информация о них.

Вопрос: Как правильно вытянуть информацию из БД? Одним запросом здесь не обойтись, а если и можно как-то, то не хочется, так как запрос будет тяжелым. Из таблицы «wall_element» я получу id, например, всех фото, или всех видео, которые прикреплены к записи на стену. Какими именно запросами лучше всего вытаскивать эти id? И какими запросами вытаскивать данные из таблицы image_item, т.е. как потом вытащить данные о фото?

P.S. Как все вытащить я, конечно, знаю, но нужны другие точки зрения и желательно более опытных разработчиков.

UPD: Все таки напишу как я это вижу:

1) Сделаю первый запрос, которым вытаскиваю 10-20 записей из таблицы «wall» — запрос очень легкий, даже при несколько миллонов записей проблем не должно возниктуть.

2) В цикле по этим 10-20 записям вытаскиваю данные из таблицы «wall_element». В каждой итерации я буду получать список фото, записей в блог, новостей и т.д. Теперь нужно обработать эти данные, т.е. отдать их каждой компоненте, к которой они принадлежат и получить обратно html. Но нужно как-то еще погрупировать вот те самые 20фото — и получать информацию с таблицы «image_item» или 20 запросами (очень легкими, поскольку запрос будет идти по id) или как-то более елегантно одним запросом… Вот так в одной итерации я собираю html разных структурных единиц компонент…

Т.е. здесь есть плюсы в том, что запросы легкие, а минусы в том, что запросов будет много для рендеринга страницы… Кэширование, конечно, никто не отменял, но нужно сначала сделать все как можно правильней…
  • Вопрос задан
  • 4506 просмотров
Комментировать
Подписаться 5 Оценить Комментировать
Пригласить эксперта
Ответы на вопрос 8
Yavanosta
@Yavanosta
UNION не так уж и плох. Особенно UNION ALL, где не требуется выбирать уникальные записи. По крайней мере он будет не медленнее чем выполнить каждый запрос по отдельности, за счет того, что может теоретически быть выполнен параллельно на нескольких ядрах.

Но если не подходит, давайте от печки. Вы постулируете следующее «мы не можем выбирать все в одном запросе, чтобы не мешать модели».

Значит минимальное количество запросов получается равным количеству_типов_данных. Для этого нужно выполнить по отдельности запросы которые у меня в UNION, но тогда вручную в коде сортировать эту кашу по датам и группировать по wall_id. Это плохой путь.

Предлагаю такой вариант:
SELECT
	w.id,
	w.description,
	COUNT(i.id) i_cnt,
	COUNT(bp.id) bp_cnt
FROM wall w

INNER JOIN wall_element we_i
        ON we_i.wall_id = w.id
INNER JOIN image i
        ON i.id = we_i.element_id

INNER JOIN wall_element we_bp
        ON we_bp.wall_id = w.id
INNER JOIN blog_post bp
        ON bp.id = we_bp.element_id

ORDER BY w.timestamp

GROUP BY w.id, w.description


Мы получаем список записей, и количество связанных с каждой записью единиц контента. Дальше разделяем ответственность в коде следущим образом:
1. основной код выполняет этот запрос, и бежит по результатам. Смотрит, что есть в конкретной записи. Например видит что картинка, и товар из магазина
2. основной код вызывает соответствующие рендереры, передавая им идентификатор записи wall_id.
3. рендерер сам (своим запросом) достает уже те данные, которые ему нужны оттуда, откуда хочет, в ту модель, которая ему нравится. Это полностью развязывает рендереры друг от друга и от вызывающего кода.

Итого имеем запросов: количество_записей_на_стене * среднее_количество_единиц_контента + 1. Думаю у вас в среднем 1 запись на стене будет иметь ссылку на 1 единицу контента (например пяток фоток, которые рендерер картинок сможет вытащить одним запросом).

Имеем в среднем запросом количество_записей_на_стене + 1. Вполне приемлимо.
Ответ написан
14 комментариев
14 комментариев
jarvis
@jarvis
Я бы сделал так:(читать до конца)
1)создал бы таблицу wall с полями id, author_user_id, author_user_name, owner_user_id, wall_content, где
-id идентификатор записи в таблице-
— author_user_id — идентификатор пользователя создавшего пост
-author_user_name его имя, внесем сюда же, чтобы не делать лишний запрос к таблицам пользователей
-owner_user_id — идентификатор владелец стены
-wall_record_content — контент записи. представляет собой json с полями title, description,images, видео, опросы и что у ваc там есть. пример 

content: {
title: SuperProPost,
Mysuper_Puper_Images: [{
Photo_link1, Photo_link1

}],
Mysuper_Puper_Video_links[{
videolink1, videolink2....
}]
и т.д,
}


Структура на самом деле будет немного посложней, но я думаю вы справитесь.
И потом уже в самом скрипте обрабатывать этот json и формировать шаблон. При записи и изменении скриптом формировать этот json и записывать его в БД,

В итоге все будет получено одним запросом, нагрузка на БД будет минимальна. Без джойнов.
Select  * from wall_1234(лучше использовать шардинг чтобы не было по 20 млн записей в одной таблице) where owner_id=Owner_id_from request Limit 0, 20 (к примеру)

На каждые 50 пользователей выделять отдельную таблицу wall_groupId. Таким образом все данные для стены будут получены, внимание, в ОДИН запрос из таблицы на 100-300 тысяч записей, за что БД скажет нам спасибо
Я надеюсь мысль ясна, подкорректируйте под свои нужды.
Ответ написан
4 комментария
4 комментария
jarvis
@jarvis
Из таблиц не надо тянуть для всей стены, слишком тяжело.
На счет «кэширования»? А в чем проблема закэшировать ответ на мой запрос? Тот же memcashed отлично закэширует результаты запросы 
Select  * from wall_1234 where owner_id=Owner_id_from_request Limit 0, 20

Ладно, давайте я объясню механизм подробнее. В поле «wall_record_content» вся фишка. Оно нам крайне необходимо и именно оно позволяет нам очень легко выводить данные. Но для этого нам придется заплатить тем, что вводить их будет немного сложнее. Но это не страшно, в соц сети намного чаще читают чем пишут.

Начинаем.
Пользователь создает пост на стене друга.

Готовим запрос на сервер. Вначале кипятим воду включаем личные данные пользователя как автора(имя и id в приложении) и id друга для поля id_owner_user в запрос. Отлично, мы молодцы, но это еще не все.

Дальше пользователь вводит описание. Ничего себе. Тоже вставляем эти данные в параметр description нашего запроса. Идем дальше.
Пользователь прикрепляет фото и видео. Чтобы можно было просматривать фото нам необходим адрес маленькой картинки(в дальнейшем буду называть его «превьюшкой» )и адрес страницы, где мы будем просматривать фото в полном размере. Допустим у нас есть несколько способ прикрепления фото: драг-эн-дропом, если фото уже было добавлено на сервер, или сразу с локального компьютера(или телефона)

Если фото было ранее загружено на сервер (то есть эти данные уже записаны в БД), то просто с помощью ajax запроса получаем эти данные из БД.(Возможно и не потребуется, если на страницк у нас уже есть необходимые данные для фото). Полученные данные вставляем в параметр массив Post_images.

Если нет, то ПЕРВЫМ ДЕЛОМ ЗАГРУЖАЕМ ФОТО НА СЕРВЕР с помощью ajax запроса(с помощью него будет проще) и вносим данные о фото в таблицу images. Когда это будет сделано, возвращаем данные ajax запросом. Включаем их в параметр Post_images. Неплохо.

Теперь у нас есть параметр в котором содержатся данные о фотографиях, которые прикреплены к посту.

Аналогично с видео и аудио. Супер!

Теперь у нас есть полностью сформированные Post запрос с параметрами author_user_id, author_user_name, owner_id, post_description, Post_images, Post_videos. Отправляем на сервер. И не забываем про CSRF-защиту. Мы любим своих пользователей и заботимся о них.

На сервере принимаем данные запрос, обрабатываем его и записываем в БД как я писал в первом посте.
Пример json-a, которые попадет в БД.
content: {
description: Срочно оцени эти супер-крутые фотки! ,
Mysuper_Puper_Images: [{
Photo_link1{
preview:"images.site.com/dsfgdfgsdg.jpg"
link:"www.site.com/viewphoto/345345345345"
},
Photo_link2{
preview:"images.site.com/dsfgdfgsdg.jpg"
link:"www.site.com/viewphoto/345345345345"
}

}],
Mysuper_Puper_Video_links[{
videolink1{}, videolink2{}
}]
и т.д,
}

Вот и все, теперь у нас в базе лежит пост пользователя.

В сеть выходит друг Павел Недуров. Первым делом он идет на свою стену, чтобы просмотреть, что симпатичные девчонки написали ему за день. При загрузке его стены мы формируем запрос 
Select  * from wall_1234 where owner_id=Owner_Павел НедуровID Limit 0, 20


Наш скрипт получает из базы( или memcashed) результат выполнения этого запроса. Обрабатываем эти данные и выводим их в нашем шаблоне, не забывая все экранировать для предотвращения xss атак. Мы любим пользователей!

В итоге на странице будет выведено:
1)текст записи
2)маленькие картинки, по клику на превьюшку переходим на страницу, где можно просмотреть большую версию.
3)маленькие картинки с кадром из видео, по клику переходим на страницу, где можно просмотреть видео(хм, где-то я уже это слышал)

Пост друга Павлу понравился! Но вот незадача, симпатичные девчонки сегодня ничего не написали. Не горюй, бро, твое время еще придет. А на этом наша программа подходит к концу. В следующей передаче раз мы будем делать новостную ленту. Оставайтесь на связи.

В итоге благодаря моему способу очень легко и быстро выводить записи, ведь все данные мы получаем одним запросом. И легко кэшировать. И индексы тоже можно использовать. Картинки хранятся в таблице images, видео — в videos. Все можно легко просмотреть. Но с реализацией ввода поста придется немного попотеть, но результат того стоит.

P. S. Не принимайте ничего на свой счет, это у меня просто такой стиль.
Ответ написан
3 комментария
3 комментария
hell0w0rd
@hell0w0rd
Просто разработчик
Использовать JOIN'ы или что вы хотите услышать?
Ответ написан
1 комментарий
1 комментарий
Yavanosta
@Yavanosta
Как можно втянуть «те 20 фото которые вы добавили на стену» если у вас нет идентификатора пользователя нигде.

Если просто все фото на стене, то так:
SELECT
	w.id,
	w.description,
	we.*
FROM
	wall w
INNER JOIN wall_element we
	ON w.id = we.wall_id


Это то, что вы хотели?
Ответ написан
28 комментариев
28 комментариев
hell0w0rd
@hell0w0rd
Просто разработчик
То, что вы пишите в вопросе, и если так делаете — это индусский код. Не надо решать задачи SQL посредством PHP, или другого языка. Тем более запросы к бд в цикле/рекурсии, еще как-то.
У вас должно быть как с классами — каждый класс отвечает за свое — так и в базах данных, каждая таблица отвечает за свое. В таблице картинок вы храните все что нужно по картинкам, в таблице видео, ну вы поняли. А вот в таблице постов должна быть связь со всеми составляющими поста, а также связь с автором.
Мартин Грубер — Понимание SQL — говорят что перевод не самый лучший. Однако мне книга очень понравилась, и вам советую.
Ответ написан
10 комментариев
10 комментариев
Yavanosta
@Yavanosta
Кстати простите за оффтопик, но я вот бегло посмотрел ваш сайт. Первой глянул на вот это:
excalibur.com.ua/blog/php-c3/%D0%B3%D0%B5%D0%BD%D0%B5%D1%80%D0%B0%D1%82%D0%BE%D1%80-%D0%BF%D0%B0%D1%80%D0%BE%D0%BB%D0%B5%D0%B9-%D0%BD%D0%B0-php-i22.html

Вы пытаетесь определить ту, что быстрее работает, и в обоих ВНУТРИ цикла делаете count($arr) и strlen($chars). Вы не думали, что если вынести подсчет длинны массива и длинны строки за цикл, то у вас функция ускорится примерно в $length раз? И вы еще после этого кого-то упрекаете в отсутствии опыта?
Ответ написан
3 комментария
3 комментария
@L3n1n
Если я правильно понял.

Одним запросом через джоины вытаскиваете все данные. Поле у которого какой например description= NULL говорит о том что это не запись а элемент записи(фото, видео...). В цикле когда обрабатываете запрос сразу же видите как его рендерить.
То есть вся идея в том, что вы результат запроса сводите к одному формату который уже кодом направляете куда вам нужно.
Ответ написан
1 комментарий
1 комментарий
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы
Вакансии с Хабр Карьеры
PHP-разработчик
Decart IT-production
от 260 000 до 340 000 ₽
Разработчик PHP
Автомакон
от 206 000 ₽
Backend-разработчик PHP
Wanted. • Москва
До 160 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Создать анимацию 3д в которой идет монтаж двери
22 нояб. 2024, в 09:18
10000 руб./за проект
Обработать фото в стиле ASCII
22 нояб. 2024, в 08:04
1 руб./за проект
Сверстать десктоп версию сайта в tailwind
22 нояб. 2024, в 06:06
1500 руб./в час
Ещё заказы