Форум MySQL

Доброго времени суток!
Есть задача: в личном кабинете пользователь может зайти на страницу СООБЩЕНИЯ и должен увидеть сообщения в виде слудюющей таблицы:

Контакт Сообщений Непрочитанные Последнее Последнее сообщение
_________________________________________________________________________________________________

Алексей 6 есть 11.12.2009 Вам пишет Алексей смоленский. Рад соо...
Андрей 1 нет 10.12.2008 Уважаемый Андрей, не могли бы вы быть так ...
Катерина 8 есть 12.08.2009 Я самая самая красивая и умная на всем б...

Контакт - это имя контакта, который написал сообщение мне или которому написал сообщение я. В этом и возникла сложность, если бы сообщения только приходили ко мне, то можно было скруппировать сообщения по адресату, а здесь нужно выводить имя контакта. с которым я контактирую. Например второе сообщение в примере я написал Андрею, но выводится не моё имя, а Андрея.

Таблица сообщений:

CREATE TABLE `message` (   `id` int(255) NOT NULL auto_increment,   `from_user` int(255) NOT NULL,   `to_user` int(255) NOT NULL,   `text` text NOT NULL,   `date_ins` datetime NOT NULL,   `read` int(2) NOT NULL,   PRIMARY KEY  (`id`) )

from_user - id контакта, от кого письмо.
to_user - id контакта. кому письмо.
read - если еденица, то сообщение прочитано, если 0 - не прочитано.

Пример заполнения:

INSERT INTO `message` VALUES (1, 180, 25, '11111111 1111111 1111111111111 111111111', '2009-12-04 23:42:23', 1); INSERT INTO `message` VALUES (2, 21, 180, '22222222 322222222222 222222222', '2009-12-05 23:42:24', 0); INSERT INTO `message` VALUES (3, 30, 180, '444444444444 444444444 4444444444', '2009-12-06 23:42:25', 0); INSERT INTO `message` VALUES (4, 21, 180, '333333333 333333333333 333333333', '2009-12-07 23:42:26', '1');

Хочется получить данный результат за один запрос. Подскажите. Не хочется реализовывать это посредствоь php.

К моему великому сожалению слудующий запрос работает неправильно.
Таблица заполнена следущими данными:

INSERT INTO `message` VALUES (1, 180, 25, '111 111 111 111', '2009-12-04 23:42:23', 1); INSERT INTO `message` VALUES (2, 21, 180, '222 222 222 222', '2009-12-05 23:42:24', 0); INSERT INTO `message` VALUES (3, 30, 180, '444 444 444 444', '2009-12-06 23:42:25', 0); INSERT INTO `message` VALUES (4, 21, 180, '333 333 333 333', '2009-12-07 23:42:26', 0); INSERT INTO `message` VALUES (5, 180, 26, '555 555 555 555', '2009-12-08 23:42:27', 0); INSERT INTO `message` VALUES (6, 180, 25, '666 666 666 666', '2009-12-09 23:42:28', 1); INSERT INTO `message` VALUES (7, 18, 25, '777 777 777 777', '2009-12-10 23:42:29', 0); INSERT INTO `message` VALUES (8, 25, 180, '888 888 888 888', '2009-12-11 15:33:08', 0);

делаю запрос:

SELECT    `message`.`from_user`,   `message`.`to_user`,   count(*) AS `cnt`,   max(date_ins) AS `last_message`,   sum(case `read` when true then 0 else 1 end) AS `cnt_not_read` FROM   `message` WHERE   `from` = '180' OR    `to` = '180' GROUP BY   `message`.`from_user`,   `message`.`to_user`

и получаю результат:
from_user to_user cnt last_message cnt_not_read
---------------------------------------------------------------------------------------------------
  21    180   2 07.12.2009 23:42 2
  25    180   1 11.12.2009 15:33 1
  30    180   1 06.12.2009 23:42 1
  180   25   2 09.12.2009 23:42 0
  180    26   1 08.12.2009 23:42 1

Очевидно, следует сперва написать два запроса (для входящих и для исходящих)
А потом уже составлять из них один общий - сложный.

Что для вас очевидно - для меня тайна, покрытая мраком. Не могли бы помочь запросиком?

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

Написал вот аткой запрос, но результат такой же. как и у предыдущего

(SELECT     `message`.`from_user`,    `message`.`to_user`,    count(*) AS `cnt`,    max(date_ins) AS `last_message`,    sum(case `read` when true then 0 else 1 end) AS `cnt_not_read`  FROM    `message`  WHERE    `from_user` = '180' GROUP BY    `message`.`to_user`) UNION ALL (SELECT     `message`.`from_user`,    `message`.`to_user`,    count(*) AS `cnt`,    max(date_ins) AS `last_message`,    sum(case `read` when true then 0 else 1 end) AS `cnt_not_read`  FROM    `message`  WHERE    `to_user` = '180' GROUP BY    `message`.`from_user`)

не понимаю как логически это релизовать

Насчет результат такой же - враки.

Спешите... Сказал же - сперва один запрос! Потом только - другой.
Вот это у Вас запрос для исходящих. Так?

SELECT     `message`.`from_user`,    `message`.`to_user`,    count(*) AS `cnt`,    max(date_ins) AS `last_message`,    sum(case `read` when true then 0 else 1 end) AS `cnt_not_read`  FROM    `message`  WHERE    `from_user` = '180' GROUP BY    `message`.`to_user`

Если убрать `message`.`from_user` из SELECT то будет вполне себе симпатично. Ну еще агрегат в sum() можно попроще написать. sum(`read` = false)

И таки да. Полного внешнего соединения (FULL OUTER JOIN) в MySQL нету. А посему вариант с двумя независимыми запросами (и формированием FULL OUTER JOIN средствами php) оказывается предпочтительным.

Вот запрос для исходящих:

SELECT      `message`.`to_user`,     count(*) AS `cnt`,     max(date_ins) AS `last_message`,     sum(`read` = false) AS `cnt_not_read`   FROM     `message`   WHERE     `from_user` = '180'  GROUP BY     `message`.`to_user`

Результат:
to_user___________cnt_________last_message______cnt_not_read
  25_____________2__________09.12.2009 23:42________0
  26_____________1__________08.12.2009 23:42________1

Вот запрос для входящих:

SELECT      `message`.`from_user`,     count(*) AS `cnt`,     max(date_ins) AS `last_message`,     sum(`read` = false) AS `cnt_not_read`   FROM     `message`   WHERE     `to_user` = '180'  GROUP BY     `message`.`from_user`

Результат:
from_user_____cnt___last_message____cnt_not_read
  21_________2___07.12.2009 23:42______2
  25_________1___11.12.2009 15:33______1
  30_________1___06.12.2009 23:42______1

Написал вот такой запрос:

SELECT `new_table`.`contact`, SUM(cnt_message) AS `cnt_message`, `new_table`.`cnt_not_read`, `new_table`.`last_message`, `new_table`.`last_message_text`  FROM ( (SELECT   `message`.`from_user` as `contact`,   count(*) AS `cnt_message`,   sum(`read` = '0') AS `cnt_not_read`,   max(date_ins) AS `last_message`,   SUBSTRING(`text`,1,20) as `last_message_text` FROM   `message` WHERE   `message`.`to_user` = '180'  GROUP BY   `message`.`from_user`) UNION (SELECT   `message`.`to_user` as `contact`,   count(*) AS `cnt_message`,   sum(`read` = '0') AS `cnt_not_read`,   max(date_ins) AS `last_message`,   SUBSTRING(`text`,1,20) as `last_message_text` FROM   `message` WHERE   `message`.`from_user` = '180'  GROUP BY   `message`.`to_user`) ) AS `new_table` GROUP BY `new_table`.`contact` ORDER BY `last_message` DESC;

Но есть маленькая загвоздка: не могу понять, как взять текст именно последнего собщения данного контакта.

SELECT   `message`.`from_user` as `contact`,   count(*) AS `cnt_message`,   sum(`read` = '0') AS `cnt_not_read`,   max(date_ins) AS `last_message`,   SUBSTRING(`text`,1,20) as `last_message_text` FROM   `message` WHERE   `message`.`to_user` = '180'  GROUP BY   `message`.`from_user`

т.е. дата берётся последнего сообщения, а текст сообщения первого попавшегося. А нужно. чтобы текст брался того сообщения, которое последнее. Подскажиет пожалуйсто.

Нельзя указывать в списке SELECT негруппирующие поля, кроме тех что в аргументах агрегатных функций.
SUBSTRING - функция обычная, неагрегатная.

Хорошо, а как можно сделать тогда?

а автоинкрементный первичный ключ в таблице сообщений есть?
Лучше ориентироваться на него, а не на дату.

SELECT   `message`.`from_user` as `contact`,   count(*) AS `cnt_message`,   sum(`read` = '0') AS `cnt_not_read`,   max(id) AS `last_id` FROM   `message` WHERE   `message`.`to_user` = '180'  GROUP BY   `message`.`from_user`

ну и дальше подсоединить таблицу JOINом по условию равенства ключей.

SELECT ..., m.date_ins , SUBSTRING(m.`text`...)    FROM      (       SELECT ...     ) AS p      JOIN messages AS m ON p.last_id = m.id