PostgreSQL JOIN с типом массива с порядком элементов массива, как его реализовать?

У меня есть две таблицы в базе данных:

CREATE TABLE items(
 id SERIAL PRIMARY KEY,
 ... some other fields
);

В этой таблице содержится строка данных с уникальным идентификатором.

CREATE TABLE some_chosen_data_in_order(
 id SERIAL PRIMARY KEY,
 id_items INTEGER[],

);

В этой таблице содержится поле типа массива. Каждая строка содержит значения идентификаторов из таблицы items в определенном порядке. Например: {2,4,233,5}.

Теперь я хочу получить данные из таблицы items для выбранной строки из таблицы some_chosen_data_in_order с порядком для элементов в типе массива.

Моя попытка была ПРИСОЕДИНЯЙТЕСЬ:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?

Вторая попытка была подзапросом вроде:

SELECT I.* FROM items AS I 
WHERE I.id = ANY 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])

Но ни один из них не сохраняет идентификаторы в том же порядке, что и в поле массива. Не могли бы вы мне помочь, как получить данные из таблицы items, чтобы соответствовать порядку идентификаторов массива из таблицы some_chosen_data_in_order для определенной строки?

Ответ 1

SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id

Вышеуказанный запрос выбирает элементы из таблицы items с идентификаторами: 1,2,3,2,3,5 в этом порядке. Это то, что вы имели ввиду?:)

Ответ 2

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

Модуль int_array contrib имеет функцию idx, которая даст вам позицию индекса int в массиве. Также есть функция idx в snippets wiki, которая работает для массива любых типов данных.

SELECT i.*, idx(id_items, i.id) AS idx
FROM some_chosen_data_in_order s
JOIN items i ON i.id = ANY(s.id_items)
ORDER BY idx(id_items, i.id)

Ответ 3

SELECT I.* FROM items AS I 
WHERE I.id IN (SELECT UNNEST(id_items) FROM some_chosen_data_in_order 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])