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

NCIAC · 12.12.2009 06:04:52

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

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

Алексей 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

vasya · 13.12.2009 01:04:29

SELECT * FROM (

(SELECT

  `message`.`from_user` as `Контакт`,

  count(*) AS `Кол-во сообщений`,

  if(sum(`read`)=`Кол-во сообщений`,'нет','есть') as `Непрочитанные`,

  max(date_ins) AS `Последнее`,

  SUBSTRING(`text`,1,20) as `Последнее сообщение`

FROM

  `message`

WHERE

  `from_user` = '180' 

GROUP BY

  `message`.`from_user`)

UNION

(SELECT

  `message`.`to_user` as `Контакт`,

  count(*) AS `Кол-во сообщений`,

  if(sum(`read`)=`Кол-во сообщений`,'нет','есть') as `Непрочитанные`,

  max(date_ins) AS `Последнее`,

  SUBSTRING(`text`,1,20) as `Последнее сообщение`

FROM

  `message`

WHERE

  `to_user` = '180' 

GROUP BY

  `message`.`to_user`)

) x ORDER BY `Последнее`;

NCIAC · 13.12.2009 09:01:32

Спасибо большое. С вашей помощью написал аот такой запрос:

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`

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

vasya · 13.12.2009 15:43:33

Да, это я упустил из виду. Если мы выбираем поле, которое не имеет групповой функции и не входит в GROUP BY, то сервер выбирает произвольные записи. Стандарт SQL вообще запрещает такие запросы.

В данном случае придется использовать объединение.

select b.*, SUBSTRING(`a`.`text`,1,20) as `last_message_text` from `message` a JOIN

(SELECT

  `message`.`from_user` as `contact`,

  count(*) AS `cnt_message`,

  sum(`read` = '0') AS `cnt_not_read`,

  max(date_ins) AS `last_message`

FROM

  `message`

WHERE

  `message`.`to_user` = '180'

GROUP BY

  `message`.`from_user`) b

ON (a.from_user = b.from_user AND a.date_ins = b.date_ins);

4e1ovek · 20.12.2010 17:43:38

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

Смысл в том, что необходимо получить из таблицы (`message`) где хранятся сообщения всех пользователей, последние сообщения из разговора (либо отправленное мной, либо собеседником – зависит от того чьё было последним). В нашем же случае мы получаем последнее сообщения, но написанное только лишь собеседником.

vasya · 20.12.2010 22:50:40

Если вы внимательно посмотрите запросы в постах #2 и #3, то увидите, что запросы там состаят из 2ух частей (через union), как раз для того, чтобы получать сообщения либо отправленные, либо полученные.
А последнее мое сообщение - это иллюстрация как нужно доработать запрос на примере одной из его частей.

4e1ovek · 21.12.2010 01:59:38

Угу, в том то и дело, что не выходит.

--

-- Структура таблицы `mail`

--

CREATE TABLE IF NOT EXISTS `mail` (

  `id_mail` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор сообщения',

  `id_from` int(10) unsigned NOT NULL default '0' COMMENT 'Идентификатор отправителя',

  `id_to` int(10) unsigned NOT NULL default '0' COMMENT 'Идентификатор получателя',

  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Дата',

  `text` text NOT NULL COMMENT 'Текст сообщения',

  `status` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Статус',

  PRIMARY KEY  (`id_mail`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Сообщения' AUTO_INCREMENT=10 ;

--

-- Дамп данных таблицы `mail`

--

INSERT INTO `mail` (`id_mail`, `id_from`, `id_to`, `date`, `text`, `status`) VALUES

(1, 1, 2, '2010-11-21 18:24:50', 'займись делом!', 0),

(2, 2, 1, '2010-11-23 10:25:02', 'Пожалуйста, перепиши мне фотки питерские на диск до встречи, очень хочется уже их посмотреть.', 0),

(3, 1, 2, '2010-12-18 22:25:31', 'У меня нет, Алёна, ни виниловых пластинок, ни кассет, ни дисков... привезу фотки в Москву.', 0),

(4, 2, 1, '2010-12-19 22:25:37', 'Жень, мне Антонио звонил, он тебе звонил, а телефон выключен был.', 0),

(5, 3, 4, '2010-12-20 17:24:22', 'Сообщение от Светланы к Катюше.', 0),

(6, 4, 3, '2010-12-20 18:30:39', 'Сообщение от Катюше к Светлане.', 0),

(7, 4, 1, '2010-12-19 19:05:42', 'От Катюши к Женьке', 0),

(8, 1, 4, '2010-12-21 03:37:39', 'От Женьки к Катюше', 0),

(9, 3, 1, '2010-12-20 19:06:26', 'От Светке к Женьке', 0);

Получившийся запрос:

SELECT

    SUM(cnt_message) AS `cnt_message`,

    n.cnt_not_read,

    n.last_message,

    n.last_message_text

FROM

(

    (

        SELECT b.*, a.text AS `last_message_text` FROM `mail` a

        JOIN 

        (

            SELECT `id_from` as `contact`,count(*) AS `cnt_message`,sum(`status` = '0') AS `cnt_not_read`,max(date) AS `last_message` 

            FROM `mail` WHERE `id_to`='1' GROUP BY `id_from`

        ) b

        ON (a.id_from = b.contact AND a.date = b.last_message)

    )

    UNION

    (

        SELECT b.*, a.text AS `last_message_text` FROM `mail` a

        JOIN 

        (

            SELECT `id_to` AS `contact`,count(*) AS `cnt_message`,sum(`status` = '0') AS `cnt_not_read`,max(date) AS `last_message` 

            FROM `mail` WHERE `id_from`='1' GROUP BY `id_to`

        ) b

        ON (a.id_to = b.contact AND a.date = b.last_message)

    )

) AS `n`

GROUP BY

    n.contact

ORDER BY

    n.last_message DESC

На выходе получаем:

   1    2010-12-20 19:06:26    От светке к женьке

   2    2010-12-19 22:25:37    Жень, мне Антонио звонил, он тебе звонил, а телефо...

   1    2010-12-19 19:05:42    От катюши к женьке

Отредактированно 4e1ovek (21.12.2010 21:24:47)

4e1ovek · 21.12.2010 20:22:04

Кажется то что нужно:

SELECT

    SUM(cnt_message) AS `cnt_message`,

    n.cnt_not_read,

    n.last_message,

    n.last_message_text

FROM

(

    (

        SELECT b.*, a.text AS `last_message_text` FROM `mail` a

        JOIN

        (

            SELECT `id_from` AS `contact`,count(*) AS `cnt_message`,sum(`status` = '0') AS `cnt_not_read`,max(`date`) AS `last_message`

            FROM `mail` WHERE `id_to`='1' GROUP BY `id_from`

        ) b

        ON (a.id_from = b.contact AND a.date = b.last_message)

    )

    UNION

    (

        SELECT b.*, a.text AS `last_message_text` FROM `mail` a

        JOIN

        (

            SELECT `id_to` AS `contact`,count(*) AS `cnt_message`,sum(`status` = '0') AS `cnt_not_read`,max(`date`) AS `last_message`

            FROM `mail` WHERE `id_from`='1' GROUP BY `id_to`

        ) b

        ON (a.id_to = b.contact AND a.date = b.last_message)

    ) ORDER BY last_message DESC

) AS `n`

GROUP BY

    n.contact

Отредактированно 4e1ovek (21.12.2010 20:22:33)

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

#1 12.12.2009 06:04:52

Группировка в таблицах

#2 13.12.2009 01:04:29

Re: Группировка в таблицах

#3 13.12.2009 09:01:32

Re: Группировка в таблицах

#4 13.12.2009 15:43:33

Re: Группировка в таблицах

#5 20.12.2010 17:43:38

Re: Группировка в таблицах

#6 20.12.2010 22:50:40

Re: Группировка в таблицах

#7 21.12.2010 01:59:38

Re: Группировка в таблицах

#8 21.12.2010 20:22:04

Re: Группировка в таблицах

Board footer