PostgreSQL JOIN with array type with array elements order, jak zaimplementować?
Mam dwie tabele w bazie danych:
CREATE TABLE items(
id SERIAL PRIMARY KEY,
... some other fields
);
Ta tabela zawiera wiersz come data o unikalnym ID.
CREATE TABLE some_chosen_data_in_order(
id SERIAL PRIMARY KEY,
id_items INTEGER[],
);
Ta tabela zawiera pole typu array. Każdy wiersz zawiera wartości ID z tabeli items w określonej kolejności. Na przykład: {2,4,233,5}.
Teraz chcę pobrać dane z tabeli items dla wybranego wiersza z tabeli some_chosen_data_in_order z kolejnością elementów typu array.
Moja próba dołączyła:
SELECT I.* FROM items AS I
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?
Druga próba była subquery like:
SELECT I.* FROM items AS I
WHERE I.id = ANY
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order WHERE id = ?])
Ale żaden z nich nie przechowuje identyfikatorów w takiej samej kolejności jak w polu array. Czy mógłbyś mi pomóc, jak uzyskać dane z tabeli items z odpowiadającym im Idem tablicy kolejność z tabeli some_chosen_data_in_order dla konkretnego wiersza?
4 answers
SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id
Powyższe zapytanie wybiera pozycje z tabeli items o idach: 1,2,3,2,3,5 w tej kolejności.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-27 03:12:11
Prawdopodobnie normalizacja twojego stołu byłaby najlepszą radą, jaką mogę ci dać.
Moduł contrib int_array posiada funkcję idx, która daje pozycję indeksu int w tablicy. Istnieje również funkcja idx na wiki snippets , która działa dla tablic dowolnego typu danych.
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-03-22 04:50:09
select distinct on (some_chosen_data_in_order.id)
some_chosen_data_in_order.*,
array_to_json( array_agg(row_to_json( items))
over ( partition by some_chosen_data_in_order.id ))
from some_chosen_data_in_order
left join items on items.id = any (some_chosen_data_in_order.id_items)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-27 05:04:59
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 = ?])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-16 10:28:10