В PostgreSQL: функции count() строк с помощью 2-уровень соединения с ONLY_FULL_GROUP_BY активный

В PostgreSQL: функции count() строк с помощью 2-уровень соединения с ONLY_FULL_GROUP_BY активный

12.10.2019 10:21:42 Просмотров 44 Источник

Проблема

У меня возникли проблемы с написанием функционального запроса подсчета строк результатов с 2-уровневым соединением.

Таблица базы данных

У меня есть следующие (упрощенные) таблицы (среда разрабатывается в Drupal 8 btw, используя службу базы данных):

Таблица nodes:

id - числовой идентификатор
title-варчар

Примеры записей:

id   title
1    My first article
2    My second article
3    My third article

Таблица comments:

cid - числовой идентификатор
entity_type-varchar, комментируемая сущность
entity_id - числовой идентификатор, содержащий ссылку
status-int, 0 для неопубликованных, 1 для опубликованных
comment - текст

Примеры записей:

cid   entity_type   entity_id   status   comment
1     node          1           1        foo
2     node          1           1        bar
3     comment       1           1        baz
4     node          1           0        spam/foul language/whatever
5     node          2           1        yeeeha

Описание структуры данных

"Узлы" можно комментировать. Затем комментарии сохраняются в таблице "комментарии". Для каждого комментария есть выделенная строка, содержащая идентификатор комментария, тип объекта commented (может быть "узел" и "комментарий") и идентификатор объекта commented. И комментарии также могут быть прокомментированы - эти " ответы "также сохраняются в таблице" комментарии", поэтому эти записи содержат" комментарий " в качестве entity_id и идентификатора комментария, на который ответили.

Теперь я хотел бы получить следующий результат с помощью одного запроса:

id    title                comments
1     My first article     3
2     My second article    1
3     My third article     0

commentsдолжны содержать сумму всех опубликованных комментариев и опубликованных ответов на данный узел. Таким образом, если узел непосредственно прокомментирован дважды, и один из этих комментариев также прокомментирован, счетчик commentsдолжен указывать 3. (Примечание: atm "отвечает" на комментарии, на которые нельзя ответить, поэтому здесь есть только 3-уровневая среда (node< comment< comment)).

Используемая база данных:

Используемая база данных-PostgreSQL 9.6 с ONLY_FULL_GROUP_BY):

select

Но для жизни меня я просто не могу понять правильный способ написания запроса. Моя основная идея состоит в том, чтобы выбрать узлы базовой таблицы, слева присоединиться к первому этапу комментариев по этому вопросу, а затем снова слева присоединиться к ответам на первое соединение. Но похоже, что у моей базы данных есть другие идеи о моих запросах... \_(ツ)_/

Я очень надеюсь, что там есть кто-то, кто может вернуть меня в нужное русло. Любая помощь очень ценится! Спасибо, что нашли время прочитать все это.

У вопроса есть решение - Посмотреть?

Ответы - В PostgreSQL: функции count() строк с помощью 2-уровень соединения с ONLY_FULL_GROUP_BY активный / PostgreSQL: COUNT() rows using 2-level joins with ONLY_FULL_GROUP_BY active

Является ответом!
GMB

12.10.2019 10:33:43

Ваша логика довольно хороша. Вам просто нужно подсчитать различные значения на первом уровне и правильно обрабатывать NULLзначения. Кроме того , вы хотите группировать столбцы, поступающие для nodes, а не из comments.

select 
    n.id,
    n.title,
    coalesce(count(distinct c1.cid), 0) + coalesce(count(c2.cid), 0) "comments"
from nodes n
left join comments c1 
    on  c1.entity_id = n.id  
    and c1.entity_type = 'node'
    and c1.status = 1
left join comments c2 
    on c2.entity_id = c1.cid  
    and c2.entity_type = 'comment'
    and c2.status = 1
group by n.id, n.title

Этот демо на ДБ возиться с ваш пример возвращает данные:

| id  | title             | comments |
| --- | ----------------- | -------- |
| 1   | My first article  | 3        |
| 2   | My second article | 1        |
| 3   | My third article  | 0        |
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58357767#comment103068644_58357767
Привет @GMB! Во-первых, позвольте мне поблагодарить вас за вашу помощь! Я пробовал ваше предложение, но кажется, что что-то немного не так в моей фактической реализации. См. фактический сгенерированный запрос от Drupal в этом pastebin . Проблема с этим заключается в том, что на самом деле один и тот же узел появляется несколько раз в результирующем наборе
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58357767#comment103068762_58357767
Кроме того, у меня нет выбора, кроме комментариев groupBy, а также-как изначально было заявлено, only_full_group_by действует.
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58357767#comment103068941_58357767
@Chris: я не могу получить доступ к ссылке pastebin. Тем не менее, я сделал несколько исправлений к запросу (отсутствующие условия status = 1) и протестировал его в скрипке. Теперь он возвращает ожидаемые результаты. Смотрите мой обновленный ответ.
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58357767#comment103068970_58357767
@Chris: правило, которое ONLY_FULL_GROUP_BYBY. Нет необходимости включать столбцы таблицы GROUP BYв запрос, так как они отображаются в выражениях агрегации.
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58357767#comment103068977_58357767
О, боже! @GMB, мне очень жаль. Мой ответ на ваш ответ был основан на простой опечатке, которую я сделал, пробуя ваше предложение. Вместо группировки entity_id я сделал ошибку и сгруппировал cid-неудивительно, что один и тот же узел был указан несколько раз! Спасибо, ваша помощь вернула меня на дорогу!
Bipin Kumar

12.10.2019 11:14:05

Прежде всего, давайте поймем, что вы написали и пропустили-в запросе вы пропустили a (после подсчета в первой строке.

Во-вторых, вы делали group by с дополнительным "r. entity_id", который не требуется, и он сегрегирует результат.

Третий порядок по результату по идентификатору узла в порядке возрастания.

Пожалуйста, используйте ниже запрос и отметьте правильный ответ, если это поможет вам.

SELECT n.id, n.title, COUNT(c.cid)+COUNT(r.cid) AS comments
FROM nodes n
 left JOIN comments c
ON c.entity_type = 'node' AND n.id = c.entity_id AND c.status = 1
 left JOIN comments r
ON r.entity_type = 'comment' AND c.cid = r.entity_id AND r.status = 1
GROUP BY n.id, n.title, c.entity_id 
order by n.id asc
https://stackoverflow.com/questions/58357672/postgresql-count-rows-using-2-level-joins-with-only-full-group-by-active/58358096#comment103070147_58358096
Привет @Бипин Кумар! Спасибо за ваши усилия, чтобы помочь мне! :- ) Я понимаю замечания, которые вы сделали, они имеют большое значение для меня и помогают мне лучше понять соединения! GMB просто был на несколько минут быстрее с его ответом, и его ответ также помог мне решить мою проблему. Но я действительно ценю время, которое вы потратили, чтобы прочитать мой вопрос и оказать полезную помощь!
Закрыть X