Postgres Lateral Join Несколько таблиц для ограничения результатов

У меня есть вопрос о боковых соединениях в Postgres.

Мой вариант использования: я хочу вернуть набор данных, который объединяет несколько таблиц, но ограничивает количество возвращаемых публикаций и обзоров. Упрощенная схема таблицы ниже

Автор таблицы

  • ID
  • ИМЯ

Просмотр таблицы

  • ID
  • AUTHOR_ID
  • PUBLICATION_ID
  • СОДЕРЖАНИЕ

Публикация таблицы

  • ID
  • ИМЯ

Таблица AuthorPublication

  • AUTHOR_ID
  • PUBLICATION_ID

Итак, для моего первоначального запроса у меня есть это:

SELECT
  a.id,
  a.name
    json_agg (
      json_build_object (
        'id', r.id,
        'content', r.content 
       )
    ) AS reviews,
    json_agg (
      json_build_object(
        'id', p.id, 
        'name', p.name
        )
    ) AS publications
FROM
  public.author a
INNER JOIN
  public.review r ON r.author_id = a.id
INNER JOIN
  public.author_publication ap ON ap.author_id = a.id 
INNER JOIN 
  public.publication p ON p.id = ap.publication_id
WHERE 
  a.id = '1'
GROUP BY
  a.id

Это возвращает нужные мне данные, например, я получаю имя автора, идентификатор и список всех его обзоров и публикаций, которым они принадлежат. Что я хочу сделать, так это ограничить количество обзоров и публикаций. Например вернуть 5 отзывов и 3 публикации.

Я пытался сделать это с помощью бокового запроса, но столкнулся с проблемой, когда, если я выполняю один боковой запрос, он работает по назначению.

так как:

INNER JOIN LATERAL
 (SELECT r.* FROM public.review r WHERE r.author_id = a.id LIMIT 5) r ON TRUE

Это возвращает набор данных только с 5 отзывами, но если я добавлю второй боковой запрос

INNER JOIN LATERAL
 (SELECT ap.* FROM public.author_publication ap WHERE ap.author_id = a.id LIMIT 5) r ON TRUE

Теперь я получаю 25 результатов как для обзоров, так и для публикаций с повторными/дублированными данными.

Итак, мой вопрос: разрешено ли вам иметь несколько боковых соединений в одном запросе PG, и если нет, то как можно ограничить количество результатов из JOIN?

Спасибо!


postgresql join lateral
person KukicAdo    schedule 12.02.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы должны изменить свой запрос на что-то вроде этого:

SELECT
  a.id,
  a.name,
  (
    SELECT
        json_agg ( r )
      FROM (
             SELECT 
                  json_build_object (
                      'id', r.id,
                      'content', r.content 
                  ) AS r
               FROM public.review r 
              WHERE r.author_id = a.id
              ORDER BY r.id
              LIMIT 5
           ) AS a
  ) AS reviews,
  (
   SELECT
        json_agg (p)
     FROM (
            SELECT
                 json_build_object(
                    'id', p.id, 
                     'name', p.name
                 ) AS p
              FROM public.author_publication ap 
             INNER JOIN public.publication p ON p.id = ap.publication_id
             WHERE ap.author_id = a.id
             ORDER BY p.id
             LIMIT 3
        ) AS a
   ) AS publications
FROM
  public.author a
WHERE 
  a.id = '1'
person Roman Tkachuk    schedule 12.02.2017
comment
Когда я обновляю свой запрос до того, что вы предложили, я получаю следующую ошибку: `` столбец r.id должен появиться в предложении GROUP BY или использоваться в агрегатной функции ``` и добавление GROUP BY дает мне: ``` [Err] ОШИБКА: более одной строки возвращается подзапросом, используемым как выражение ``` Но если я просто ограничусь 1, я получу только один результат. - person KukicAdo; 13.02.2017
comment
Еще одна интересная вещь, которую я обнаружил, — это выполнение следующего запроса: SELECT json_agg(r.uuid) FROM public.review r WHERE r.author_uuid = 'b3113a70-e4bf-4b30-a9ca-9b0530a3115b' LIMIT 1 Creates создает массив json со всеми совпадающими полями. Но если я удалю json_agg, я получу только 1 строку с первым полем. - person KukicAdo; 13.02.2017
comment
Извините, что-то неудобно писать запрос без структуры db. Когда вы зададите следующий вопрос, пожалуйста, предоставьте оператор CREATE TABLE, некоторые типичные данные и ожидаемый результат. - person Roman Tkachuk; 13.02.2017
comment
Привет Роман - сделанное тобой обновление работает отлично! Спасибо большое за вашу помощь. Это именно то, что мне нужно. :) - person KukicAdo; 13.02.2017