PostgreSQL JOIN with array type with array elements order, how to implement?
- by Adiasz
Hello
I have two tables in database:
CREATE TABLE items(
id SERIAL PRIMARy KEY,
... some other fields
);
This table contains come data row with unique ID.
CREATE TABLE some_choosen_data_in_order(
id SERIAL PRIMARy KEY,
id_items INTEGER[],
);
This table contains array type field. Each row contains values of IDs from table "items" in specyfic order. For example: {2,4,233,5}.
Now, I want to get data from table "items" for choosen row from table "some_choosen_data_in_order" with order for elements in array type.
The my attempt is JOIN:
SELECT I.* FROM items AS I
JOIN some_choosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?
Second attempt was subquery like:
SELECT I.* FROM items AS I
WHERE I.id = ANY
(ARRAY[SELECT S.id_items FROM some_choosen_data_in_order WHERE id = ?])
But none of them keep IDs order in array field. Could You help me, how to get data from "items" table with correspond with array IDs order from "some_choosen_data_in_order" table for specyfic row?
