Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Форумы портала PHP.SU :: Версия для печати :: Двойной запрос! нужна помощь
Форумы портала PHP.SU » PHP » SQL и Архитектура БД » Двойной запрос! нужна помощь

Страниц (1): [1]
 

1. Renya - 27 Декабря, 2016 - 07:05:31 - перейти к сообщению
Приветствую Вас!

Помогите пожалуйста с запросом, голову сломал уже.

имеется таблица с диалогами юзера и таблица сообщений, выполняя запрос по выводу диалогов пользователя через WHERE dialog.id IN (1,2,5,8)

хочу получить как в ВК вывести список диалогов с последним сообщением в диалоге

мой запрос выводить диалоги и список всех сообщений чередуя по времени

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT dialog.id, dialog.users, msg.id, msg.sey_to, msg.msg, msg.STATUS, msg.date FROM dialog, msg WHERE dialog.id IN (SELECT dialog_id FROM msg) AND dialog.id=msg.dialog_id AND dialog.id IN ($dialogs) ORDER BY msg.date DESC


т.е. сперва выводит последнее сообщение диалога 1 потом 5,8,5,8,1 учитывая последнее сообщение по времени.

Помогите составить запрос чтоб он выводил по одному последнему, а не все сообщения диалогов. пробовал ставить LIMIT 1 тут (SELECT dialog_id FROM msg) вывода нет.
2. Renya - 27 Декабря, 2016 - 23:19:55 - перейти к сообщению
Спасибо всем за внимания, вопрос решил так:

как я понял WHERE dialog.id IN (1,2,5,8) так не получиться

поэтому через foreach раскидал список нужных диалогов и после уже сделал такой запрос для каждого диалога

CODE (SQL):
скопировать код в буфер обмена
  1. SELECT dialog.id, dialog.users, msg.id, msg.sey_to, msg.dialog_id, msg.msg, msg.STATUS, UNIX_TIMESTAMP(msg.date) FROM (SELECT MAX(id) AS 'maxid', IF (dialog_id = '".$val."', msg, date) AS 'user' FROM msg WHERE dialog_id = '".$val."' GROUP BY user ORDER BY msg.date DESC LIMIT 1) AS a INNER JOIN msg ON a.maxid = id, dialog WHERE dialog.id=msg.dialog_id


вывел последнее сообщение диалога, ID диалога и список юзеров в диалоге

З.ы. и все таки если кому то что придет в голову) отпишитесь
3. Мелкий - 28 Декабря, 2016 - 08:01:27 - перейти к сообщению
Штука под интенсивное чтение и значительно более редкую запись - добавьте в диалоги поле last_message_id со ссылкой на соответственно последнее сообщение.
4. Renya - 28 Декабря, 2016 - 08:18:11 - перейти к сообщению
Мелкий предлагаете после каждого сообщение обновлять запись диалога?
соединений многовато будет записать сообщение и обновить запись диалога. не вариант. проще так оставить как есть. можно еще сократить запрос убрав запрос к диалогам. список все равно получаю намного раньше.
5. Мелкий - 28 Декабря, 2016 - 10:52:51 - перейти к сообщению
Да, обновлять. В той же транзакции, можно триггером на after insert.
Ну, если у вас пользователи странные и никогда не читают то что пишут... Ммм, а нафиг вам тогда список диалогов понадобился?
А типичная картина веба - чтение на порядок преобладает над записью. Поэтому сильно упростить чтение обходится дешевле небольшого утяжеления записи.

Простор для уменьшения числа апдейтов есть, если принять возможность построения eventual consistency системы. Т.е. апдейтить не сразу, а накапливать по какому-то таймеру, например, секунд в 5. Диалог, в котором за 5 секунд понаписали столько сообщений, что эта схема оказалось дешевле триггера? Серьёзно? У вас чат для роботов?

N запросов (вероятно, N+1, откуда-то id диалогов вы же узнаёте?) с группировкой по вычисляемому полю - это будет больно. Честно, я вообще не понял, зачем там понадобилась группировка аж по тексту сообщения и почему она так странно написана через if, который всегда true по определению фильтра в where.

Поиграться с oltp получением последнего сообщения можно. Только нагенерируйте сперва мелкую табличку, ну миллионов в 10 хотя бы сообщений и с правдоподобным распределением сообщений по диалогам.
CODE (SQL):
скопировать код в буфер обмена
  1. SELECT /**/ FROM dialogs JOIN (
  2.     SELECT dialog_id, max(id) AS lastid FROM msg WHERE dialog_id IN (/**/) GROUP BY dialog_id
  3. ) lastmsgs ON dialogs.id = lastmsgs.dialog_id
  4. JOIN msg ON msg.id=lastid

Дальнейшее зависит от СУБД. Если диалоги селективны - то может и так сойдёт.
Лучше бы субд умела loose insex scan напрямую или как-то обучить.
Можно поиграться с lateral join, если ваша субд его умеет.
6. Renya - 28 Декабря, 2016 - 11:07:32 - перейти к сообщению
Мелкий извиняюсь, я не профи. данный запрос нашел в просторах и добавил свое.

ID диалогов я получаю за ранее вывожу список диалогов юзеру в которых он участвует почему отдельно?!

дело в том что планирую делать групповой чат и поэтому я храню в диалоге юзеров через запятую те кто общается в этом диалоге. чтоб потом просто сокету указать отправляем этому диалогу а там уже всем кто подписан на этот диалог.

мне нужно просто вывести список диалогов в котором участвует юзер с последним сообщением в этом диалоге
7. Мелкий - 28 Декабря, 2016 - 12:44:09 - перейти к сообщению
Renya пишет:
ID диалогов я получаю за ранее вывожу список диалогов юзеру в которых он участвует почему отдельно?!

Так если заранее - это и будет первый запрос в N+1 реализации. Этот запрос, плюс N зависимых.

Renya пишет:
дело в том что планирую делать групповой чат и поэтому я храню в диалоге юзеров через запятую те кто общается в этом диалоге. чтоб потом просто сокету указать отправляем этому диалогу а там уже всем кто подписан на этот диалог.

Совершенно не понял, зачем для pub/sub схемы чата понадобилось так криво хранить список участников и зачем это нужно. Добавили новое сообщение - отправили событие в этот чат. Все подписчики сообщение получили.
К тому же так криво хранить список участников. Искать невозможно, апдейтить неудобно.

По табличке сообщений участников искать громоздко и всё равно понадобится внешняя структура для более лёгкого хранения связей. Можно туда и воткнуть id последнего сообщения.
user_dialogs: primary key(dialog_id, user_id), last_message_id
upsert'ить при написании нового сообщения в диалоге. По этой табличке элементарно ищутся и пользователи-участники и последние сообщения в диалогах как самого пользователя, так и вообще по чатику.
8. Renya - 28 Декабря, 2016 - 19:58:05 - перейти к сообщению
архитектура диалогов у меня получилось так:

юзер щелкает написать сообщение там я ищу в диалогах есть ли открытый диалог между ними (возможно когда то уже переписывались и удалили историю сообщений) и возвращаю ID диалога либо нового только что созданного либо старого. создаю канал прослушки данного диалога по ID между пользователями.

встречал схему диалогов, но только между двумя пользователями. а мне нужно реализовать и групповой чат 3,4 и т.п.
в итоге схема диалогов имеет только два поля ID и users где сохраняю участников данного диалога. кроме как сохранять их через запятую ничего не придумал.

схема сообщений хранит кто отправил сообщение в каком диалоге.
при отправке сообщения сохраняю в базе после отправляю в ID диалога, где те кто в сети уже подписаны на этот диалог получают его.

вообще это выглядит как в ВК переписка принцип тот же.

Я прекрасно понимаю что все это далеко не идеально, поэтому и обращаюсь на форум.
9. Мелкий - 28 Декабря, 2016 - 21:06:19 - перейти к сообщению
Не надо упорно ссылаться на вк, я там не разработчик и вообще не зарегистрирован. Поэтому для меня пустой звук, что и как там сделано.

Хранение чего угодно через запятую - фигня, приносящая только головную боль и ничем не помогающая.
Идеально чаты хранятся только в /dev/null, остальное компромиссы.

Нормальная форма:
диалоги: суррогатный ключ
сообщения: id диалога, id автора, дата, сообщение

Оптимизация под чтение:
диалоги: суррогатный ключ, дата старта. Напрямую в запросах не участвует, кроме создания нового диалога, за ненадобностью
пользователи диалога: id диалога, id пользователя, id последнего сообщения (кстати, default null для наблюдателей)
сообщения: id, id диалога, id автора, дата, сообщение
Возможно ещё промежуточные аггрегаты, например с счётчиками сообщений, если нужны.

Оптимизация записи:
см. нормальную форму, вполне сгодится для преобладания пишущей нагрузки

 

Powered by ExBB FM 1.0 RC1