Как правильно составить SQL запрос?

Question

[entermix]

Хочу организовать групповые беседы, есть таблицы:

users // Пользователи
id, first_name, last_name

dialogs // Беседы
id, author_id, subject

dialogs_users // Участники беседы
dialog_id, user_id

dialog_messages // Сообщения диалога
id, dialog_id, user_id, text

message_views // Просмотры сообщений
id, message_id, user_id

Пользователь создает диалог, потом может пригласить туда несколько пользователей, при чтении сообщений, в таблицу message_views заносится соответствующая запись

Помогите пожалуйста составить SQL запрос для выборки всех диалогов пользователя (в которых он принимал участие), а так-же количества непрочитанных сообщений, где количество сообщений всего > 0

Это можно сделать 1 запросом?

Comments

[JSinga]

"а так-же количества непрочитанных сообщений, где количество сообщений всего > 0" - а какое поле отвечает за то что сообщение прочитано или нет?

Должен быть флажок хоть какой то.

[entermix]

JSinga: Если в таблице message_views есть запись, значит прочитано

Answer 1

[Dmitry Bay]

dialogs
id, author_id, subject

dialogs_users
dialog_id, user_id

почему так? у диалога может быть несколько пользователей?

Comments

[Dmitry Bay]

а вообще, запрос на все диалоги:
select o.* , i.* from dialogs i
inner join user o on i.author_id=o.id
where o.id=123

не вижу переключения что сообщение прочитано.

[entermix]

Дмитрий Бай,

>почему так? у диалога может быть несколько пользователей?
Да, групповые беседы

>не вижу переключения что сообщение прочитано.
Если в таблице message_views есть запись, значит прочитано определенным пользователем

[JSinga]

entermix:
>почему так? у диалога может быть несколько пользователей?
я думал в dialogs хранится только автор диалога, тема диалога и id
а в dialogs_users все участники

[entermix]

JSinga:
Так и есть, а в message_views хранятся просмотры пользователей для каждого из сообщений

Answer 2

[JSinga]

select d.subject, count( mv.message_id) from users u
inner join dialogs_users du on ( u.id = du.user_id)
inner join dialogs d on ( d.id = du.dialog_id)
inner join dialog_messages dm on ( dm.dialog_id = d.id and dm.user_id = u.id)
inner join message_views mv on ( mv.message_id = dm.id and mv.user_id = u.id)
where
u.id = ?
Having count( mv.message_id) > 0 --can delete in our case

Вместо вопроса подставлять нужный id user - а. Если нужно всех то просто уберите последнюю строку и добавьте вывод юзера в select.
Я склепал ответ из двух полей первое это тема диалога, 2 кол-во непрочтенных в этом диалоге, по сути должно работать хоть и выглядит жутко:)

Comments

[JSinga]

entermix: поправил запрос, в первоначальном была неточность

[entermix]

Запрос:
select `d`.`subject`, count( `mv`.`message_id`) from `users` `u`
inner join `dialogs_users` `du` on ( `u`.`id` = `du`.`user_id`)
inner join `dialogs` `d` on ( `d`.`id` = `du`.`dialog_id`)
inner join `dialog_messages` `dm` on ( `dm`.`dialog_id` = `d`.`id` and `dm`.`user_id` = `u`.`id`)
inner join `dialog_message_views` `mv` on ( `mv`.`message_id` = `dm`.`id` and `mv`.`user_id` = `u`.`id`)
where
count( `mv`.`message_id`) > 0
and `u`.`id` = 1

выдает ошибку:
#1111 - Invalid use of group function

[Constantine]

JSinga:
>count( mv.message_id) > 0
Запрос не проверял, но это по идее лишнее. Со связью INNER, если нет сообщений то и в выборку не попадет. По идее.
Что скажете?

[JSinga]

еще раз подправил запрос, having можно стереть можно оставить.

Constantine
Да, вы правы.
В этом случае Having не обязателен.
И да, именно причину вы верную указали.

Answer 3

[Виталий Драч]

SELECT d.*, (SELECT COUNT(1) FROM dialog_messages AS dm WHERE NOT EXISTS (SELECT * FROM message_views AS mv WHERE mv.message_id = dm.id AND user_id = <ПОЛЬЗОВАТЕЛЬ> ) WHERE dm.dialog_id = d.id ) AS messageCount
FROM dialogs AS d
INNER JOIN dialogs_users AS du ON du.dialog_id = d.id
WHERE user_id = <ПОЛЬЗОВАТЕЛЬ>

Comments

[entermix]

Запрос:
"SELECT `d`.*, (SELECT COUNT(1) FROM `dialog_messages` AS `dm` WHERE NOT EXISTS (SELECT * FROM `message_views` AS `mv` WHERE `mv`.`message_id` = `dm`.`id` AND `user_id` = 1 ) WHERE `dm`.`dialog_id` = `d`.`id` ) AS `messageCount`
FROM `dialogs` AS `d`
INNER JOIN `dialogs_users` AS `du` ON `du`.`dialog_id` = `d`.`id`
WHERE `user_id` = 1"

выдает ошибку:
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `dm`.`dialog_id` = `d`.`id` ) AS `messageCount` FROM `dialogs` AS `d` INNE' at line 1 "