VPF::Группировка в таблицах - Форум программистов

NCIAC

Дата 11.12.2009, 10:22 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 55
Регистрация: 7.2.2009

Репутация: нет
Всего: нет

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

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

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

Имя контакта берётся из таблицы users:

Код


CREATE TABLE `users` (
  `id` int(255) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

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

skyboy

Дата 11.12.2009, 11:08 (ссылка)

(голосов:1)

Загрузка ...

неОпытный

Профиль
Группа: Модератор
Сообщений: 9820
Регистрация: 18.5.2006
Где: Днепропетровск

Репутация: 41
Всего: 260

делается два одинаковых по списку возвращаемых полей запроса. и объединяются в один через оператор UNION. Сортируется уже объединенный запрос.

Добавлено через 32 секунды
даже лучше "union all" в твоем случае.

Aramor

Дата 11.12.2009, 20:04 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 2
Регистрация: 24.9.2009

Репутация: нет
Всего: нет

del

Это сообщение отредактировал(а) Aramor - 11.12.2009, 20:33

NCIAC

Дата 11.12.2009, 20:34 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 55
Регистрация: 7.2.2009

Репутация: нет
Всего: нет

Основная сложность не в том, как объеденить две таблицы, а в том, как сгруппировать таблицу, чтобы вибирались уникальные id контактов. То есть, если в таблице есть такие записи:
from         to
180          25
180          25
180          31
180          37
37            180
45            180
, то должны выбраться слкдующие значения:
25,31,37,45
и соответсвенно должно быть подсчитано количество писем в переписке с каждым контактом
25(2), 31(1), 37(2), 45(1)

Akina

Дата 11.12.2009, 22:22 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 106
Всего: 454

Подзапрос

Код


Select from_user, 
       to_user, 
       count(*) cnt, 
       max(date_ins), 
       sum(case read when true then 0 else 1 end) cnt_not_read
From message
group by from_user, to_user

Что он возвращает - по-моему понятно.
Его join с таблицей для получения остальных данных.

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

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

NCIAC

Дата 12.12.2009, 00:51 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 55
Регистрация: 7.2.2009

Репутация: нет
Всего: нет

Цитата(Akina @ 11.12.2009, 22:22)

Подзапрос

Код


Select from_user, 
       to_user, 
       count(*) cnt, 
       max(date_ins), 
       sum(case read when true then 0 else 1 end) cnt_not_read
From message
group by from_user, to_user

Что он возвращает - по-моему понятно.
Его join с таблицей для получения остальных данных.

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

Код


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

Это сообщение отредактировал(а) NCIAC - 12.12.2009, 06:16

NCIAC

Дата 12.12.2009, 15:57 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 55
Регистрация: 7.2.2009

Репутация: нет
Всего: нет

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

Код


(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`)

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

Akina

Дата 12.12.2009, 17:22 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 106
Всего: 454

Цитата(NCIAC @ 12.12.2009, 01:51

)

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

А потому что не думаешь ни хрена... а повторять влоб обычно бессмысленно.
Тем более что тебе явно намякнуто, что надо дорабатывать.
Если ты в поле `read`кладёшь 0 или 1 - почему оставил case when TRUE?

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

NCIAC

Дата 13.12.2009, 09:13 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 55
Регистрация: 7.2.2009

Репутация: нет
Всего: нет

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

Код


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`

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

Akina

Дата 13.12.2009, 19:33 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 106
Всего: 454

Сначала доработай мой подзапрос - чтобы он давал строго то что нужно.
Потом inner join его по всем совпадающим полям с исходной таблицей - и бери из неё недостающие поля.

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

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MySQL \| Следующая тема »