Форум пользователей MySQL

Aldo · 20.03.2011 17:15:55

Доброго времени суток!

Помогите, пожалуйста, с составлением sql-запроса. Задача следующая:

Есть 2 таблицы (members, pm)

members

uid -- nickname

1 Sergey
2 Alex
3 Anna

pm

pmid -- sender -- recipient -- read -- create (VARCHAR) -- folder

1 3 2 1 1300611957 inbox
2 3 2 1 1300611957 outbox
3 2 3 0 1300614959 inbox
4 2 3 0 1300614959 outbox
5 2 1 0 1300614981 inbox
6 2 1 0 1300614981 outbox
7 2 3 0 1300616349 inbox
8 2 3 0 1300616349 outbox

sender - отправитель
recipient - получатель
read - прочтено(1), непрочтено(0)
create - дата внесения сообщения

Задача:

Пользователь Alex(2) просматривает персональные сообщения

Необходимо выбрать контакты в виде:

Anna

кол-во сообщений
прочтено либо нет

Sergey

кол-во сообщений
прочтено либо нет

К примеру, возьмем первый контакт - Anna. Должно выводиться общее число сообщений не только от Anna к Alex, но и наоборот - от Alex к Anna.

И контакты должны выводиться в зависимости от даты внесения сообщений. Например, сейчас первым контактом идет Anna. Но, если через некоторое время Sergey напишет сообщение Alex, либо Alex напишет Sergey - контакт с именем Sergey будет показан первым...

Для решения данных задач делаю следующий запрос:

$member_id = 2;

$nickname = 'Alex';

$sql = "SELECT `pm`.`sender`, `pm`.`recipient`, `pm`.`read`, MAX(`pm`.`create`) AS `maxdate`, COUNT(*) AS `cnt`, `members`.`uid`, `members`.`nickname` FROM `pm` LEFT JOIN `members` ON `pm`.`sender` = `members`.`uid` OR `pm`.`recipient` = `members`.`uid` WHERE ((`pm`.`recipient` = " . $member_id . " AND `pm`.`folder` = 'inbox') OR (`pm`.`sender` = " . $member_id . " AND `pm`.`folder` = 'outbox')) AND `members`.`nickname` != '" . $nickname . "' GROUP BY `members`.`nickname` ORDER BY `maxdate` DESC" );

$res = mysql_query( $sql );

Выходит массив:

Array

(

    [sender] => 3

    [recipient] => 2

    [read] => 1

    [maxdate] => 1300616349

    [cnt] => 3

    [uid] => 3

    [nickname] => Anna

)

Array

(

    [sender] => 2

    [recipient] => 1

    [read] => 0

    [maxdate] => 1300614981

    [cnt] => 1

    [uid] => 1

    [nickname] => Sergey

)

Как видно из первого массива, поле `maxdate` выводится верно. Самое свежее сообщение внесено - 1300616349. В этой строке, поле `read` = 0, а фактически выводится ошибочное `read` = 1. Т.е. `read` берется из ранних сообщений (pmid = 1 или pmid = 2).

Планировал делать выборку внутри while, получая `read` из строки с `create` равное `maxdate`. Но число собеседников может и будет расти, следственно, множественные выборки в цикле ни к чему хорошему не приведут...

Помогите, пожалуйста, составить правильный запрос к БД. В MySQL я новичок, целую неделю бьюсь над этой задачей, а решить никак не могу. Уверен, мой запрос выше не совсем (а может и совсем) корректно составлен, несмотря на то, что частично выполняет задачу...

Заранее большое спасибо!

Отредактированно Aldo (20.03.2011 17:22:56)

paulus · 21.03.2011 00:19:29

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

SELECT sender, MAX(create) AS create FROM pm GROUP BY 1.

К этому подзапросу можно прицепить и остальные данные:

SELECT pm.* FROM pm JOIN (...) sub ON (pm.sender = sub.sender AND pm.create = sub.create).

Постарайтесь не использовать LEFT JOIN там, где он не нужен. Постарайтесь не
использовать VARCHAR там, где храните числа. Постарайтесь не хранить числа
там, где по смыслу дата (TIMESTAMP).

Есть еще какая-то санта-барбара вот тут — не знаю, поможет ли Вам.

Aldo · 21.03.2011 16:35:54

paulus написал:
Насколько я понял, у Вас проблема с тем, чтобы вытащить данные последней
строки по пользователю. Плясать надо от простых запросов, которые Вы можете
сформулировать и написать. Например,

SELECT sender, MAX(create) AS create FROM pm GROUP BY 1.

К этому подзапросу можно прицепить и остальные данные:

SELECT pm.* FROM pm JOIN (...) sub ON (pm.sender = sub.sender AND pm.create = sub.create).

Есть еще какая-то санта-барбара вот тут — не знаю, поможет ли Вам.

Спасибо Вам за примеры! Экспериментировал до сего момента, пробовал создавать разные запросы, но тщетно. Видимо, мой теперешний уровень знаний не дает возможность прыгнуть выше собственной головы. Прочел и четыре страницы из темы "Проблема с Select", которая внесла еще большую сумятицу.

members

uid -- nickname

1 Sergey
2 Alex
3 Anna

Слегка изменил таблицу `pm`:

pm

pmid -- sender -- recipient -- read -- readtime -- create

1 2 1 1 1300708629 1300708489
2 2 3 1 1300708606 1300708508
3 2 3 1 1300708606 1300708526
4 2 1 1 1300708629 1300708547
5 3 2 0 0 1300708610
6 1 2 0 0 1300708634

Задача осталась прежней:

Вывести список контактов для пользователя с именем Alex, к примеру. Вывод контактов должен быть в виде:

Имя пользователя
кол-во сообщений
прочтено либо нет
время прочтения

Что мне необходимо решить:

1. Получить даты последних сообщений на каждого пользователя;

В данном случае, на двух пользователей - Sergey и Anna. В независимости от того, кто отправил последнее сообщение - Sergey -> Alex, либо Alex -> Sergey. Anna -> Alex, либо Alex -> Anna.

2. По дате последнего сообщения необходимо узнать значение поля `read` - прочтено сообщение либо нет. Т.е. 0 либо 1. Собственно, именно по этой причине и необходимо мне узнать дату последнего сообщения на каждого пользователя;

3. Получить имя пользователя (выбрать `uid`, `nickname` из таблицы `members`, да так, чтобы `uid` != '2' (uid пользователя Alex) и `nickname` != 'Alex' (с целью не выводить контакт с именем Alex - а вывести только его собеседников);

4. Получить кол-во сообщений на каждый контакт;

5. Вывести контакты, сортируя по дате сообщений (по убыванию);

В идеале, в результате запроса должен получиться массив следующего содержания:

[read] - прочтено последнее сообщение либо нет
[readtime] - время прочтения сообщения
[cnt] - кол-во сообщений

Array

(

    [sender] => 1

    [recipient] => 2

    [read] => 0

    [readtime] => 0

    [maxdate] => 1300708634

    [cnt] => 3

    [uid] => 1

    [nickname] => Sergey

)

Array

(

    [sender] => 3

    [recipient] => 2

    [read] => 0

    [readtime] => 0

    [maxdate] => 1300708610

    [cnt] => 3

    [uid] => 3

    [nickname] => Anna

)

Более наглядно:

Sergey
Сообщений: 3 (Alex отправил 2 сообщения Sergey - Sergey отправил 1 сообщение Alex)
непрочтено

--------------

Anna
Сообщений: 3 (Alex отправил 2 сообщения Anna - Anna отправила 1 сообщение Alex)
непрочтено

Контакт с именем Alex не выводится...

Мой заключительный (и 20-30-й по счету за сегодня) запрос ни к чему не привел и окончательно запутал

:

$sql = "SELECT pm.*, members.uid, members.nickname FROM pm, members JOIN(SELECT sender, recipient, MAX(create) AS create FROM pm GROUP BY 2) sub ON (pm.sender = sub.sender AND pm.create = sub.create) WHERE members.uid != 2 AND members.nickname != 'Alex'";

$res = mysql_query( $sql );

Пожалуйста, помогите составить правильный запрос. Я совсем выбился из сил. Понимаю, что мои потуги ни к чему так и не приведут. Ибо знаний недостаточно в составлении сложных запросов. Словно первоклассник перед учебником высшей математики. В очередной раз убеждаюсь, что в первую очередь необходимо мне постигать SQL, ибо именно в составлении запросов частенько застопоряюсь...

paulus написал:
Постарайтесь не использовать LEFT JOIN там, где он не нужен. Постарайтесь не
использовать VARCHAR там, где храните числа. Постарайтесь не хранить числа
там, где по смыслу дата (TIMESTAMP).

Спасибо Вам за советы, обязательно буду им следовать!

paulus · 23.03.2011 00:45:33

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

В любом случае, последнее сообщение, относящееся к Сергею:
SELECT MAX(pmid) FROM pm WHERE sender = 1 OR recipient = 1;

Дата и состояние прочтенности сообщения, соответственно,
SELECT read, readtime FROM pm WHERE pmid = (
SELECT MAX(pmid) FROM pm WHERE sender = 1 OR recipient = 1)

Количество сообщений — это по смыслу независимый запрос. Независимые по
смыслу запросы стоит делать независимыми. Но так как Вы всё равно будете
настаивать на том, чтобы сделать одним запросом (почему-то настаивают всегда
), можно записать в виде подзапроса:

Код:

SELECT 
    read, 
    readtime, 
    (SELECT COUNT(*) FROM pm WHERE sender = 1 OR recipient = 1) count
FROM pm 
WHERE pmid = (SELECT MAX(pmid) FROM pm WHERE sender = 1 OR recipient = 1)

Aldo · 23.03.2011 13:45:49

paulus написал:
Честно говоря, не понимаю, смысла Вашей задачи. Какой смысл узнавать, что
отправленное сообщение не прочтено? Нужно узнавать, прочтено ли последнее
входящее, я не прав?

Вы правы! Но хотелось бы, все же, сделать по следующей аналогии:

(прикреплено во вложении)

Т.е. идет список контактов:

Имя
статус
ссылка на страницу сообщений

-------------------------------

Имя
статус
ссылка на страницу сообщений

В данном случае, ссылка ведет на сообщения лишь двух собеседников. И имеет приблизительно следующий вид: www.mysite.ru/pm.php?uid=3

Ссылка ведет уже на страницу сообщений двух пользователей - Алекса и Анны, к примеру. Сообщения идут сплошным потоком, без разделения на входящие/исходящие:

Alex
Сообщение #1

Anna
Сообщение #2

Alex
Сообщение #3

Anna
Сообщение #4

.....

Но со страницей вывода непосредственно переписки - это я сделаю. Вся сложность именно в выводе контактов...

Несколько примеров:

1. Anna (uid=3) отправила сообщение Alex (uid=2)

Alex проверяет личные сообщения. Самым первым идет контакт с Анной:

Anna
Новые сообщения
ссылка (www.mysite.ru/pm.php?uid=3)

Действия Алекса:

a) проходит по ссылке, попадает на страницу переписки с Анной. Все сообщения от Anna -> Alex тут же помечаются, как прочитанные. Выходит...

b) проходит по ссылке, прочитывает новые сообщения, отвечает Анне;

c) Alex не прочел новое сообщение от Анны (не прошел по ссылке);

--------

Anna проверяет личные сообщения:

при варианте "a" ей выводится контакт:

Alex
Alex прочел Ваше сообщение 23.03.2011, 15:00 (к примеру)
ссылка (www.mysite.ru/pm.php?uid=2)

при варианте "b" выводится контакт:

Alex
Новые сообщения
ссылка (www.mysite.ru/pm.php?uid=2)

при варианте "c":

Alex
Alex еще не прочел Ваше сообщение
ссылка (www.mysite.ru/pm.php?uid=2)

--------

Еще пример - 2 действия:

Alex отправил сообщение Anna (uid=3)
Alex получил сообщение от Sergey (uid=1)

Alex проверяет личные сообщения:

Sergey
Новые сообщения
ссылка (www.mysite.ru/pm.php?uid=1)

---

Anna
Anna еще не прочла Ваше сообщение
ссылка (www.mysite.ru/pm.php?uid=3)

Контакты ВСЕГДА должны выводиться в порядке убывания (по дате добавления сообщений).

--------

Теперь в виде таблиц:

1. Alex получил 2 сообщения от Anna:

pmid -- sender -- recipient -- read -- readtime -- create

1 3 2 0 0 1300708489
2 3 2 0 0 1300708526

Алексу показывается как:

Anna
Новые сообщения
ссылка (www.mysite.ru/pm.php?uid=3)

Анне показывается как:

Alex
Alex еще не прочел Ваше сообщение
ссылка (www.mysite.ru/pm.php?uid=2)

2. Alex вошел на страницу сообщений - непрочитанные сообщения тут же помечаются, как прочтенные:

pmid -- sender -- recipient -- read -- readtime -- create

1 3 2 1 1300708629 1300708489
2 3 2 1 1300708629 1300708526

т.е. помечаются те сообщения, где sender = 3, recipient = 2 и read = 0, readtime = 0

Алексу показывается как:

Anna
Вы еще не ответили
ссылка (www.mysite.ru/pm.php?uid=3)

Анне показывается как:

Alex
Alex прочел Ваше сообщение ....
ссылка (www.mysite.ru/pm.php?uid=2)

3. Alex ответил Анне, Анна еще не прочла:

pmid -- sender -- recipient -- read -- readtime -- create

1 3 2 1 1300708629 1300708489
2 3 2 1 1300708629 1300708526
3 2 3 0 0 1300708610

Алексу показывается как:

Anna
Anna еще не прочла Ваше сообщение
ссылка (www.mysite.ru/pm.php?uid=3)

Анне показывается как:

Alex
Новые сообщения
ссылка (www.mysite.ru/pm.php?uid=2)

--------

Ну вот, примерно так хотелось бы реализовать...

paulus написал:
В любом случае, последнее сообщение, относящееся к Сергею:
SELECT MAX(pmid) FROM pm WHERE sender = 1 OR recipient = 1;

Дата и состояние прочтенности сообщения, соответственно,
SELECT read, readtime FROM pm WHERE pmid = (
SELECT MAX(pmid) FROM pm WHERE sender = 1 OR recipient = 1)

Спасибо! Но вот как вывести собеседников связывая 2 таблицы - pm и members? Иначе говоря, вывести результаты по каждому пользователю из тех, с кем переписывается Alex?

paulus написал:
Количество сообщений — это по смыслу независимый запрос. Независимые по
смыслу запросы стоит делать независимыми.

Я пробовал такой вариант:

$selusers = mysql_query( "SELECT uid, nickname, MAX(create) AS create FROM members, pm WHERE (pm.recipient = members.uid AND pm.sender = 2) OR (pm.recipient = 2 AND pm.sender = members.uid) GROUP BY `uid`");

Затем уже в цикле while выводить количество сообщений на каждого собеседника:

while( $row = mysql_fetch_array( $selusers ) )

    {

        $selcnt = mysql_query( "SELECT COUNT(pmid) AS cnt FROM pm WHERE (sender = 2 AND recipient = $row['uid']) OR (sender = $row['uid'] AND recipient = 2)" );

    }

Вариант удобный. НО, собеседников у Alex может быть 20-30, а и то и больше. Придется 20-30 раз выполнить запрос на подсчет кол-ва сообщений...

paulus написал:
Но так как Вы всё равно будете
настаивать на том, чтобы сделать одним запросом (почему-то настаивают всегда
)

Нет, нет - не настаиваю. Можно и два, три запроса. Просто я незнаю, хороша ли идея в цикле while несколько раз подряд обращаться к базе?

Если у Вас есть какие-либо идеи - я буду Вам очень признателен, ибо у меня пока лишь один вариант решения проблемы - множественные запросы в цикле. Правда, незнаю, хорошо ли это? Не будет ли излишней нагрузки на сервер MySQL?

Отредактированно Aldo (23.03.2011 14:15:00)

Прикрепленные файлы:

1.jpg, Размер: 21,462 байт, Скачано: 399

paulus · 24.03.2011 02:02:22

Ох, сколько Вы написали

Можно выводить в цикле, можно сделать один большой запрос, который будет
вытаскивать подзапросами (а каждый подзапрос будет независимо делать ровно
то же, что Вы написали в цикле).

Если будете упираться в производительность — все равно прийдется делать де-
нормализацию на каком-то этапе (например, придумаете «диалоги», в которых
будете сразу хранить чиселку сообщений).

Aldo · 24.03.2011 22:54:23

paulus

Спасибо! Буду обдумывать, экспериментировать...

Форум пользователей MySQL

#1 20.03.2011 17:15:55

Выборка из двух таблиц. Помогите разгадать ребус

#2 21.03.2011 00:19:29

Re: Выборка из двух таблиц. Помогите разгадать ребус

#3 21.03.2011 16:35:54

Re: Выборка из двух таблиц. Помогите разгадать ребус

paulus написал:

paulus написал:

#4 23.03.2011 00:45:33

Re: Выборка из двух таблиц. Помогите разгадать ребус

Код:

#5 23.03.2011 13:45:49

Re: Выборка из двух таблиц. Помогите разгадать ребус

paulus написал:

paulus написал:

paulus написал:

paulus написал:

#6 24.03.2011 02:02:22

Re: Выборка из двух таблиц. Помогите разгадать ребус

#7 24.03.2011 22:54:23

Re: Выборка из двух таблиц. Помогите разгадать ребус

Board footer