LINUX.ORG.RU

Написать непростой запрос

 ,


0

1

Использую СУБД Postgresql, есть таблица

https://www.db-fiddle.com/f/3ERqNCRRXJh5yEJvDtJMQf/0

CREATE TABLE forms (
	id serial PRIMARY KEY,
	account int not null,
	author text,
    doc int NOT NULL,
    users text[]
);

нужно получить результат следующего вида

author : {account: [users]}
insert into  forms(account, author, doc, users) values 
(1, 'Иванов', 3, array['Петя', 'Вася']),
(1, 'Иванов', 3, array['Иван']),
(2, 'Иванов', 3, array['Иван']),
(2, 'Сергеев', 3, array['Павел']),
(2, 'Сергеев', 3, array['Роман']),
(1, 'Сергеев', 3, array['Коля' , 'Дима'])

для предыдущего набора должны получить такой результат

'Иванов': {1: ['Петя', 'Вася', 'Иван'], 2: ['Иван']},
'Сергеев': {1: ['Коля' , 'Дима'], 2: ['Павел', 'Роман']}

Нужно написать запрос, который вернет результат с такой структурой, чтобы потом по минимуму в коде формировать такой словарь.

Нужна конструктивная идея как написать такой запрос

Или тебе строки нужны? Если строки то там есть аналогичный раздел

TDrive ★★★★★
()

Формированием json должен заниматься язык на котором у тебя построен backend.

SQL запрос select тебе всегда будет возвращать данные в табличном виде.

Далее ты в цикле должен перебрать содержимое результата выполнения запроса и преобразовать его к нужной тебе форме.

В твоём случае тебе придётся делать ассоциативные массивы, в котором ключём будет поле автор, в твоём примере Иванов, Сергеев, внутри каждого элемента ассоциативного массива будет тоже массив, ключевым полем которого будет account, а в этом массиве уже будет просто неименованный массив или даже строка в которую ты будешь дописывать значения поля users, но массив удобнее.

array(2) {
  ["иванов"] => array(2) {
    ["1"] => array(3) => {
      [0] => 'Петя'
      [1] => 'Вася'
      [2] => 'Иван'
    }
    ["2"] => array(1) => {
      [0] => 'Иван'
    }

  }
  ["Сергеев"] => array(2) {
    ["1"] => array(2) => {
      [0] => 'Коля'
      [1] => 'Дима'
    }
    ["2"] => array(2) => {
      [0] => 'Павел'
      [1] => 'Роман'
    }

  }
}

Когда в цикле переберёшь все строки результата выполнения запроса получишь массив, который ты должен будешь пройтись циклом и преобразовать в нужный тебе json.

Можешь не строить массив, а сразу создавать json посредством библиотеки и объекта json в используемом тобой языке программирования.

anonymous
()
Ответ на: комментарий от anonymous

Формированием json должен заниматься язык на котором у тебя построен backend.

Есть проекты в которых половина бекэнда на хранимках)

TDrive ★★★★★
()
Ответ на: комментарий от Aswed

я и не учу, я знаю какой это ад, но есть и адепты)

TDrive ★★★★★
()
Ответ на: комментарий от WitcherGeralt

формируй структуру в коде

Что если нужно получить только одну «страницу»? Предлагаешь перекачивать всю БД в приложение ради этого? То что такого рода запрос понадобился это конечно говорит, что архитектура говно, но это другой вопрос.

no-such-file ★★★★★
()
Ответ на: комментарий от no-such-file

Ты выдумал несуществующую проблему.

При каких это вдруг условиях тебе что-то помешает сделать пагинацию? И каким таким чудом при этом сценарии тебе в процедурке не придётся точно так же перебрать всю таблицу? В итоге единственный якобы профит будет в том, что мы исключаем передачу данных по сети. А на самом деле нихрена не профит, так как приложение несопоставимо проще масштабировать чем БД.

Ну, и более того, я щас безошибочно вангону размер сабжеврй таблицы. Он ничтожный.

WitcherGeralt ★★
()

А зачем данные в text, а не в jsonb?

anonymous
()
Ответ на: комментарий от WitcherGeralt

каким таким чудом при этом сценарии тебе в процедурке не придётся точно так же перебрать всю таблицу

Никаким. Но процедурка исполняется в СУБД и не гоняет гигабайты по сети.

приложение несопоставимо проще масштабировать чем БД

А скорость сети ты как смасштабируешь?

вангону размер сабжеврй таблицы. Он ничтожный

Возможно. Возможно даже что ТСу вообще не нужна СУБД, всё тупо в памяти можно вертеть.

no-such-file ★★★★★
()
Ответ на: комментарий от no-such-file

Сомневаюсь, что в сеть оно упрётся раньше чем в камень ли оперативу. Тем более, что трафик уже давно сжимается по дефолту, если не ошибаюсь.

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

WitcherGeralt ★★
()
Последнее исправление: WitcherGeralt (всего исправлений: 2)
select  json_build_object( author,json_object_agg (account,users) )
from (
select author,account,  array_agg( distinct usr) as users
from   forms,unnest(users) as usr
group by author,account
) t
group by author

Psilocybe ★★★★
()
Последнее исправление: Psilocybe (всего исправлений: 1)
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.