Menu
Say we create two tables (users and groups), where users are in groups: create table users ( id int unique, name varchar unique ); create table groups ( id int unique, name varchar unique, member_users int[], member_groups int[] );
AFAIK, array data types in every language preserves order.
INNER JOIN (simple join) Chances are, you've already written a statement that uses a PostgreSQL INNER JOIN. It is the most common type of join. PostgreSQL INNER JOINS return all rows from multiple tables where the join condition is met.
PostgreSQL UNNEST() function This function is used to expand an array to a set of rows.
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With