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

Ссылка скопирована
Express.js PostgreSQL SQL
1 ответ

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

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

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

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

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: там еще комментарии нужно получить, я до них еще не добрался.

Дополнительно:

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

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

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

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

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

  • 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'       }       // ...     }   ] }

    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: как я понял, это делается вне БД а на серверном языке программирования.

  • Gahoy, можно и в бд, вам же сказали join / group_concat / json_arrayagg
  • Сергей c0re, а насколько это правильно, так делать ? Мне легче сделать на сервере чем в БД.
  • Gahoy, кто-то считает что лучше стопицот простых запросов вместо одного но сложного и что якобы это быстрее, я за последнее , для того она и БД ))

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

  • Сергей c0re, а эти функции как в knex, и есть ли они вообще там, я что-то найти не смог ?
  • Gahoy, я не знаю knex , но в SQL это делается просто... - json_agg() / json_build_object(), далее обычные запросы с объединениями и группировкой.
    смотри мой ответ.
  • Сергей 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

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

    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

    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

    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

    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

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

    Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.

    Заказать помощь
    Лучший ответ
    1
    Артём Dev Ответ

    Для сбора данных с разных таблиц в базе данных (БД) вам потребуется использовать соединение таблиц. Существует несколько способов выполнения этой операции, в зависимости от ваших потребностей.

    1. Внутреннее соединение (INNER JOIN):
    INNER JOIN позволяет объединить строки из разных таблиц, у которых есть совпадающие значения в указанных столбцах. Например, если у вас есть таблицы "users" и "orders", и вы хотите получить данные о заказах и пользователях, которые сделали эти заказы, вы можете использовать INNER JOIN по идентификатору пользователя.

    SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

    SELECT users.name, orders.order_id FROM users INNER JOIN orders ON users.user_id = orders.user_id;

    2. Левое соединение (LEFT JOIN):
    LEFT JOIN возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если нет соответствия, то значения в правой таблице будут NULL. Например, если вы хотите получить список всех пользователей и их заказов (если они есть), вы можете использовать LEFT JOIN.

    SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

    SELECT users.name, orders.order_id FROM users LEFT JOIN orders ON users.user_id = orders.user_id;

    3. Правое соединение (RIGHT JOIN):
    RIGHT JOIN возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если нет соответствия, то значения в левой таблице будут NULL. Этот тип соединения редко используется, так как его можно заменить на LEFT JOIN.

    SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;

    SELECT users.name, orders.order_id FROM users RIGHT JOIN orders ON users.user_id = orders.user_id;

    4. Полное соединение (FULL JOIN):
    FULL JOIN возвращает все строки из обеих таблиц, даже если нет соответствия. Если нет соответствия, то значения будут NULL. Этот тип соединения также редко используется из-за своей сложности.

    SELECT users.name, orders.order_id
FROM users
FULL JOIN orders ON users.user_id = orders.user_id;

    SELECT users.name, orders.order_id FROM users FULL JOIN orders ON users.user_id = orders.user_id;

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

    Другие ответы (0)

    Пока нет других ответов. Будьте первым, кто поможет автору.

    Ответить на вопрос

    комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *

    Вам также может быть интересно

    VPN Как правильно настроить vless для Android TV? 0 ответов Pyrogram Как правильно зарегистрировать юзер бота в Telegram? 0 ответов печатные-платы Как заставить запускаться программу M3.exe от компании Hanxing AOI в инспекционной машине на Windows 7 Pro? 0 ответов Telegram Как диагностировать ошибку с подключением к прокси в мобильном приложении Telegram? 0 ответов