Блог

У меня есть следующие таблицы:

 CREATE TABLE sequence (
  id serial PRIMARY KEY
  -- ...other sequence data
)

CREATE TABLE sound (
  id serial PRIMARY KEY
  -- ...other sound data
)

CREATE TABLE layer (
  id serial PRIMARY KEY,
  index smallint NOT NULL,
  sequence integer NOT NULL REFERENCES sequence (id)
)

CREATE TABLE layerSound (
  id serial PRIMARY KEY,
  index smallint NOT NULL,
  layer integer NOT NULL REFERENCES layer (id),
  sound integer NOT NULL REFERENCES sound (id)
)
  

Итак, у меня есть последовательности. Каждая последовательность состоит из множества слоев. Каждый слой
имеет много layerSounds. К каждому слою звука прикреплен один звук.

Я хочу написать запрос (возвращающий JSON), который может выбрать определенную последовательность по ее идентификатору, а затем также присоединиться:

• массив слоев, используемых этой последовательностью
• агрегированный массив звуков слоев, сгруппированных по слоям
• массив уникальных звуков, используемых в этой последовательности (на всех уровнях звука)

Например:

 {
  sequence: 3,
  layers: [1, 2],
  layerSounds: [
    { layer: 1, sounds: [1, 2] },
    { layer: 2, sounds: [2, 3] }
  ],
  sounds: [
    { id: 1, foo: "bar" },
    { id: 2, foo: "baz" },
    { id: 3, foo: "blah" }
  ]
}
  

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

Мой подход до сих пор заключается в том, чтобы ВЫБРАТЬ последовательность, а затем объединить другие таблицы по отдельности. Я группирую каждый из них по идентификатору последовательности, а затем объединяю один раз с внешним запросом.

Пока это работает, я замечаю, что мне приходится повторять объединения в каждом запросе объединения, чтобы всегда группировать по sequenceId.

Поэтому, чтобы сгруппировать layerSounds по sequenceId, я объединяю layerSound в layerSound в sound . Затем я снова выполняю то же самое соединение, чтобы вычислить все звуки, используемые этой последовательностью. Я включил запрос ниже.

Мой вопрос в том, есть ли способ улучшить этот запрос? Что-то не так с этим подходом? Или повторное использование соединений, подобных этому, является нормальным?

Спасибо за ваше время.

Запрос:

 SELECT
  sequence.id,
  layers.ids AS layers,
  layerSounds.ids AS layerSounds,
  sounds.ids AS sounds
FROM sequence
JOIN (
  SELECT
    sequence,
    json_agg(id) AS ids
  FROM layer
  GROUP BY sequence
) layers ON layers.sequence = sequence.id
JOIN (
  SELECT
    sequence,
    json_agg(layerSounds) AS ids
  FROM layer
  JOIN (
    SELECT
      layerSound.layer,
      json_agg(sound.id) AS ids
    FROM layerSound
    JOIN sound
    ON sound.id = layerSound.sound
    GROUP BY layerSound.layer
  ) layerSounds ON layerSounds.layer = layer.id
  GROUP BY sequence
) layerSounds ON layerSounds.sequence = sequence.id
JOIN (
  SELECT
    sequence,
    json_agg(DISTINCT sound.id) AS ids
  FROM layer
  JOIN layerSound
    ON layerSound.layer = layer.id
  JOIN sound
    ON sound.id = layerSound.sound
  GROUP BY sequence
) sounds ON sounds.sequence = sequence.id
  

Вы определенно можете упростить запрос. Я думаю, что это одно упрощение:

 SELECT s.id, l.ids AS layers, ls.ids AS layerSounds,
       so.ids AS sounds
FROM sequence s JOIN
     (SELECT l.sequence, json_agg(l.id) AS ids,
             json_agg(ls)
      FROM layer l JOIN
           (SELECT ls.layer, json_agg(ls.sound) AS ids
            FROM layerSound ls 
            GROUP BY ls.layer
           ) ls
           ON ls.layer = l.id
      GROUP BY l.sequence
     ) l
     ON l.sequence = s.id JOIN
     (SELECT l.sequence,
             json_agg(DISTINCT ls.sound) AS ids
      FROM layer l JOIN
           layerSound ls
           ON ls.layer = l.id
      GROUP BY l.sequence
     ) so
     ON so.sequence = s.id;
  

Ключевое замечание заключается в том, что вам не нужно присоединяться к sounds , потому что информация находится внутри layerSound .

Первый подзапрос объединяет первые два подзапроса в вашей версии. В Postgres может быть способ объединить массивы JSON layer sound в один массив (возможно, используя массивы Postgres в качестве посредника). Но это делает последний список отдельным подзапросом.