ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос - число сообщений в теме
14 сообщений из 14, страница 1 из 1
Запрос - число сообщений в теме
    #35512826
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Доброго времени суток!

Хотел попросить помощи в следующем вопросе. Есть 2 таблицы топики и посты:
Топики:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE table "forum_topic" (
  "id"            serial  PRIMARY KEY,
  "title"      varchar NOT NULL,
  "author"      integer NOT NULL REFERENCES "user",
  "last_modified"      timestamp NOT NULL,
  "last_user"      integer NOT NULL REFERENCES "user"
);

и посты в них:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE table "forum_post" (
  "id"            serial  PRIMARY KEY,
  "forum_topic"   integer NOT NULL REFERENCES "forum_topic",
  "flag"                boolean
  "message"      varchar NOT NULL
  
);

У постов, как вы видите есть флаг, который может принимать значения ТРУ и ФАЛСЕ. Так вот хочется 1 запросов вытащить следующее:

Список (скажем из 10ти штук) топиков, их ИД, название а также общее число постов в каждом топике, число постов с флагом ТРУ и число постов с флагом ФАЛСЕ.

Т.е. в итоге получить таблицу:

id | title | post_quantity| true_flagged_post | false_flagged_post

Разумеется можно было бы сделать обычный СЕЛЕКТ с 3мя вложенными в него селектами, которые считают то что надо. Но такой запрос идет уже приличное время. А если у меня много постов и топиков а я еще захочу отсортировать их по числу постов, то ууу... минуты будет запрос идти.

Также если бы нужно было ТОЛЬКО число постов, то можно было бы соорудить запрос с использованием JOIN и GROUP BY:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT    forum_topic.id,
    forum_topic.title,
    COUNT(forum_post.id) AS post_quantity
FROM forum_topic
LEFT JOIN forum_post ON forum_post.forum_topic = forum_topic.id
GROUP BY forum_topic.id, forum_topic.title
ORDER BY post_quantity DESC

Но вот что делать, когда мне нужно еще посчитать число постов с определенным значением флага? Если делать еще раз ДЖОЙН таблицы forum_post получается откровенный бред...

Я пока только вижу выход держать все статистические данные (число таких постов, число сяких постов, общее число постов) в таблице топиков forum_topic и прямо оттуда их дергать. А чтобы обновлялось все четко поставить соответствующие триггерные ф-ции, которые пересчитывают нужные значения при обновлении таблицы постов.
...
Рейтинг: 0 / 0
29.08.2008, 12:27
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35512937
chAlx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chAlx
Участник
Если делать еще раз ДЖОЙН таблицы forum_post получается откровенный бред...
Почему это? Нормальная форма для sql-запроса..

Ну или функцию написать, чтобы перебирала всё подряд и считала суммы. Или вьюху вспомогательную, чтобы выводила в отдельных полях что-то для топиков 't' и 'f'..
...
Рейтинг: 0 / 0
29.08.2008, 12:57
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35512958
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
chAlx , напишите, если не затруднит... Мне никак не сообразить, я в общем то в SQL новичок совсем и непосредственно им не занимаюсь. Занимаюсь программированием на ПХП и соответсвенно мои знания SQL дальше SELECT и FROM далеко не уходят. Конечно что такое ВЬЮ и SQL ф-ция я знаю, но как сюда присобачить понимаю слабо.

Фраза "функция, которая считала бы все для всех" сразу пугает, когда данных много запросы очень долго идут, не должен же юзер ждать 40 секунд пока база соизволит ему во всех топиках кол-во постов сосчитать...
...
Рейтинг: 0 / 0
29.08.2008, 13:03
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513221
chAlx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chAlx
Участник
С тремя джойнами получится как-то так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT ft.id, ft.title, COUNT(fpall.id) AS post_quantity_all,
  COUNT(fpt.id) AS post_quantity_true,
  COUNT(fpf.id) AS post_quantity_false,
FROM forum_topic AS ft
  LEFT JOIN forum_post AS fpall ON fpall.forum_topic = ft.id
  LEFT JOIN forum_post AS fpt ON fpt.forum_topic = ft.id AND fpt.flag = 't'
  LEFT JOIN forum_post AS fpf ON fpf.forum_topic = ft.id AND fpf.flag = 'f'
GROUP BY ft.id, ft.title

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

С вьюхой вариант такой:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE OR REPLACE VIEW vw_topics AS 
SELECT ft.id, ft.title, fp.id AS pid,
  CASE WHEN fp.flag = 't' THEN fp.id END AS pid_true,
  CASE WHEN fp.flag = 'f' THEN fp.id END AS pid_false
FROM forum_topic AS ft
  LEFT JOIN forum_post AS fp ON fp.forum_topic = ft.id;

SELECT id, title, COUNT(pid) AS pids, COUNT(pid_true) AS pids_true, COUNT(pid_false) AS pids_false
FROM vw_topics
GROUP BY id, title;

Ну и самое приятное -- то, что это всё же Постгрес ;) Так что на классическом SQL не зацикливаемся:
Код: plaintext
1.
2.
3.
4.
5.
SELECT ft.id, ft.title, COUNT(fp.id) AS pids,
  COUNT(CASE WHEN fp.flag = 't' THEN fp.id END) AS pids_true,
  COUNT(CASE WHEN fp.flag = 'f' THEN fp.id END) AS pids_false
FROM forum_topic AS ft
  LEFT JOIN forum_post AS fp ON fp.forum_topic = ft.id
GROUP BY ft.id, ft.title

Razoomкогда данных много запросы очень долго идут, не должен же юзер ждать 40 секунд пока база соизволит ему во всех топиках кол-во постов сосчитать...
Да он успеет рефреш 20 раз нажать, и база упадёт ;)

В плане построения онлайн-движка типа форума такие вещи, как счётчики постов в таблице топиков, весьма актуальны. Их можно и без триггера обновлять, если на каждую вставку/удаление использовать отдельную Постгресную функцию. В ней и права можно проверить, и квоты всякие -- в общем, куда красивее получится взаимодействие с БД, чем в классических мускульных движках.
...
Рейтинг: 0 / 0
29.08.2008, 14:31
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513321
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
chAlx

Вот 1й твой вариант не прокатывает. Попробуй сам если не влом и время будет, считает черт знает что... Я 2 раза писал, ну может конечно лоханулся гдето... но писал точь в точь как ты, 3 джойна с разными псевдонимами и их пересчитываем.

А вот на счет, как я понял, уникальных возможностей ПГ, не знал про них, обязательно покурю...

Теперь касаемо постов на форумах... То что я привел это пример просто. На самом деле у меня в проекте таких вещей масса, в частности пользователи - группы пользователей. Там совершенно аналогичные запросы нужны.

Так вот есть в системе естественно опция вступления в группу, выход из группы и эти действия производятся в коде в 10ти разных местах и к сожалению не всегда работает один и тот же метод.

Поэтому все эти методы нужно дополнять соотв. куском запроса, где вызывается эта ф-ция (будь она написана как метод ПХП со своим запросом или ф-ция ПГ). И когда будешь искать все места где изменяется число юзеров в группе - обязательно где-нибудь забудешь дописать пересчет.

Поэтому и пришла в голову мысль юзать триггеры.
...
Рейтинг: 0 / 0
29.08.2008, 15:05
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513325
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Да он успеет рефреш 20 раз нажать, и база упадёт ;)
...
Рейтинг: 0 / 0
29.08.2008, 15:06
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513330
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
- да черт с ним с рефрешем... ну ты станешь тусить на форуме где страница 40 сек грузится, я думаю вряд ли ))))

Да, и спасибо за участие :)
...
Рейтинг: 0 / 0
29.08.2008, 15:06
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513347
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Да, и еще...

/* ех жаль что на форуме на этом редактирования своих постов нет - всю ветку уже загадил */

Хочется иметь возможность по всем этим полям отсортировать, возможно ли это будет сделать быстро в варианте номер 3?

Например сортировка по числу постов, когда всех этих флагов нету и используется 1 JOIN и GROUP BY проходит быстро, а вот если писать для подсчета постов вложенный селект и сортировать по результатам его работы то это песня та еще... БД, как я понимаю, сначала ДЛЯ ВСЕХ топиков подсчитывает число постов, и лишь потом сортирует, даже если мне нужно выбрать 10 первых записей из 1000...
...
Рейтинг: 0 / 0
29.08.2008, 15:11
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513450
chAlx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chAlx
Участник
RazoomВот 1й твой вариант не прокатывает.
Ну, а у меня работает. Отладку вслепую твоего варианта оставляю опытным местным телепатам ;)

RazoomПоэтому все эти методы нужно дополнять соотв. куском запроса, где вызывается эта ф-ция (будь она написана как метод ПХП со своим запросом или ф-ция ПГ). И когда будешь искать все места где изменяется число юзеров в группе - обязательно где-нибудь забудешь дописать пересчет.
Мы же говорим про то, как правильно и хорошо сделать. А если есть неправильный и плохой проект, и его не хочется переделывать -- это саавсем другой вопрос.

А когда всё-таки будешь переделывать -- пишешь пару хранимых функций add_user_to_group() и remove_user_from_group(), делаешь в них всё что надо и отнимаешь права у постгресных пользователей/групп делать это же напрямую с таблицами. Тогда никто точно не забудет, что поступает неправильно :)

RazoomБД, как я понимаю, сначала ДЛЯ ВСЕХ топиков подсчитывает число постов, и лишь потом сортирует, даже если мне нужно выбрать 10 первых записей из 1000...
Естественно -- как иначе она может узнать, что у 1001-го топика не окажется больше всего постов?

А вообще, для всех случаев, когда "нужно выбрать 10 первых записей", настоятельно рекомендуется использовать ограничитель: LIMIT 10. Это может менять план запроса и вообще крайне позитивно воспринимается планировщиком.
...
Рейтинг: 0 / 0
29.08.2008, 15:39
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513480
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Задача решена... оказывается вот как надо было:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT     "group".id,
     "group".title,
     COUNT(distinct ref_user_group."user") AS member_quant,
     COUNT(distinct na."user")
FROM "group"
LEFT JOIN ref_user_group ON ref_user_group."group" = "group".id AND ref_user_group.is_accepted = true
LEFT JOIN ref_user_group AS na ON na."group" = "group".id AND na.is_accepted = false
WHERE "group".id <  1000 
GROUP BY "group".id, "group".title
ORDER BY member_quant

// Тут группы и юзеры в них, ну да не суть важно. na - not_accepted users, юзеры которых не приняли в группу.
...
Рейтинг: 0 / 0
29.08.2008, 15:46
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513491
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Просто distinct надо было навесить, иначе он считает произведение числа записей.
...
Рейтинг: 0 / 0
29.08.2008, 15:51
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513511
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
RazoomЗадача решена... оказывается вот как надо было:

Код: plaintext
1.
2.
3.
SELECT ...
FROM "group"
LEFT JOIN ref_user_group ON ...
LEFT JOIN ref_user_group AS na ON ...
используйте вариант с "count(case ...)", многократный джоин одной и той же таблицы (ref_user_group) не нужен
...
Рейтинг: 0 / 0
29.08.2008, 15:58
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35513887
хехехе
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хехехе
Гость
Не нужно никаких дополнительных джойнов

Считайте агрегаты примерно так:

Код: plaintext
1.
2.
SUM(CASE WHEN flag THEN  1  ELSE  0  END) AS count_flag_on,
SUM(CASE WHEN flag THEN  0  THEN  1  END) AS count_flag_off
...
Рейтинг: 0 / 0
29.08.2008, 18:07
| Ответить | Цитировать | Написать  
Запрос - число сообщений в теме
    #35519748
Razoom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Razoom
Участник
Спасибо всем за ответы, буду иметь в виду конструкцию с CASE.

Однако решили все равно для подобных вещей держать дополнительное поле непосредственно в таблице групп и триггерными ф-циями это поле обновлять.
...
Рейтинг: 0 / 0
03.09.2008, 11:09
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Запрос - число сообщений в теме
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]