Как правильно собирать данные с разных таблиц БД?

Question

[Gahoy]

У меня есть такие таблицы в БД:



Как мне получить такую структуру данных из sql запроса ?

export type Author = {
  account_id: number;
  first_name: string;
  last_name: string;
}

export type Posts = {
  post_id: number;
  body: string;
  author: Author;
}

ps: так как данные находятся в разных таблицах, получается что нужно сделать сначала один запрос к базе (получить посты), потом второй (получить авторов), и далее уже соединять их в JavaScript, или можно как-то получить и через запрос SQL такую структуру ?

pss: там еще комментарии нужно получить, я до них еще не добрался.

Comments

[Дмитрий]

https://dev.mysql.com/doc/refman/8.0/en/join.html

[Gahoy]

Дмитрий, так я получу плоский список, а мне вложенность нужна.

[Дмитрий]

Gahoy, ну group_concat json_arrayagg вам в помощь, равно никто не мешает вам получить одним запросом плоскую структуру а в js собрать вложенность

[Gahoy]

Дмитрий, не знал про них, спасибо!

[Akina]

Gahoy,

так я получу плоский список, а мне вложенность нужна

Результат выполнения запроса - это абсолютно всегда плоский список/таблица.

Если надо получить "третье измерение", то это можно сделать только путём получения в поле выходного набора сериализованного значения необходимой мерности. Если достаточно одного уровня, сойдёт и CSV, иначе - никакой альтернативы, только JSON. Извращения типа создания XML строковыми функциями рассматривать не будем.

[Gahoy]

Akina, я такую структуру хочу получить:

const posts = {
  post_id: 0,
  body: 'Post Title',
  author: {
    account_id: 0,
    first_name: 'Jane',
    last_name: 'Doe'
  },
  commits: [
    {
      commit_id: 0,
      body: 'Commit message',
      author: {
        account_id: 2,
        first_name: 'Jhon',
        last_name: 'Doe'
      }
      // ...
    }
  ]
}

ps: как я понял, это делается вне БД а на серверном языке программирования.

[Сергей c0re]

Gahoy, можно и в бд, вам же сказали join / group_concat / json_arrayagg

[Gahoy]

Сергей c0re, а насколько это правильно, так делать ? Мне легче сделать на сервере чем в БД.

[Сергей c0re]

Gahoy, кто-то считает что лучше стопицот простых запросов вместо одного но сложного и что якобы это быстрее, я за последнее , для того она и БД ))

делайте как вам легче , поддерживать вам.

[Gahoy]

Сергей c0re, а эти функции как в knex, и есть ли они вообще там, я что-то найти не смог ?

[Сергей c0re]

Gahoy, я не знаю knex , но в SQL это делается просто... - json_agg() / json_build_object(), далее обычные запросы с объединениями и группировкой.
смотри мой ответ.

[Gahoy]

Сергей c0re, да спасибо, чуть позже проверю отпишусь.

Answer 1

[Сергей c0re]

Для построения такого JSON на SQL вам нужны функции json_agg() / json_build_object(), далее обычные запросы с объединениями и группировкой.

Пример как собрать JSON author

SELECT json_build_object(
    'account_id', id,
    'first_name', first_name,
    'last_name', last_name
  ) AS author
  FROM accounts

post собирается как

json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )

при объединении таблиц posts и accounts
через json_agg собираем в массив

SELECT json_agg(json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS posts
  FROM posts p
  JOIN accounts a ON a.id = p.author_id

комментарии аналогично, но с группировкой по постам - post_id

SELECT json_agg(json_build_object(
    'comment_id', c.id,
    'body', c.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS comments
  FROM comments c
  JOIN accounts a ON a.id = c.author_id
  GROUP BY post_id

далее объединяем комментарии с постами через post_id = id
для удобства комментарии вынес в CTE (общее табличное выражение)

WITH cmts AS (
SELECT json_agg(json_build_object(
    'comment_id', c.id,
    'body', c.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS comments,
  post_id
  FROM comments c
  JOIN accounts a ON a.id = c.author_id
  GROUP BY post_id
)
SELECT json_agg(json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    ),
    'comments', COALESCE(c.comments, '[]')
  )) AS "posts with comments"
  FROM posts p
  JOIN accounts a ON a.id = p.author_id
  LEFT JOIN cmts c ON c.post_id = p.id

см. пример sqlfiddle

Comments

[Gahoy]

круто, кажись работает. Правда для knex я так и не нашел этих функции...