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.
开发者_如何学JAVACREATE TABLE some_chosen_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 specific order. For example: {2,4,233,5}
.
Now, I want to get data from table items
for chosen row from table some_chosen_data_in_order
with order for elements in array type.
My attempt was JOIN:
SELECT I.* FROM items AS I
JOIN some_chosen_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_chosen_data_in_order WHERE id = ?])
But none of them keep IDs in the same order as in array field. Could you help me, how to get data from items
table with correspond with array IDs order from some_chosen_data_in_order
table for specific row?
SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id
The above query select items from items
table with ids: 1,2,3,2,3,5 in that order.
Probably normalizing your table would be the best advice I can give you.
The int_array contrib module has an idx function that will give you the int's index position in the array. Also there is an idx function on the snippets wiki that works for array's of any data types.
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)
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)
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 = ?])
If you don't like functions (https://stackoverflow.com/a/2489805/642096):
SELECT jt.*
FROM (
SELECT ARRAY_AGG(DISTINCT dupes_column) AS unique_vals
FROM dupes_table
) q
LEFT JOIN joined_table jt
ON jt.id = ANY(q.unique_vals);
精彩评论