Hi there, I'm working on a query where I need to fetch information from a table along with some data from a many-to-many connection table in a single query. My idea is to do an outer join with the connection query and aggregate the needed data in multiple 'array_agg's, and then handle this aggregated data later in code.
The issue is, there are a lot of aggs (4 by now, may increase later), and I need to order these by a 'order' field on the connection table. I can put an 'ORDER BY "order"' statement inside each 'array_agg', but I don't think that would be the most efficient way. Doing the join with a sorted connection table didn't work for me as well, probably due to other joins on the query. I tried doing some stuff with subqueries, but all attempts ended up in either failure or increased query time. The structure is a graph based structure, and both query and structure themselves are quite complex, but a simplified example would look like this: CREATE TABLE tb ( id SERIAL PRIMARY KEY ); CREATE TABLE conn ( parent_id INTEGER, child_id INTEGER, "order" INTEGER, kind INTEGER ); INSERT INTO tb VALUES (1), (2), (3); INSERT INTO conn VALUES (1, 2, 2, 10), (1, 3, 1, 20); SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind) FROM tb LEFT OUTER JOIN conn ON conn.parent_id = tb.id GROUP BY tb.id; Not sure if there's really a solution, but does anyone have any idea on how to solve this issue without doing multiple 'ORDER BY's inside each aggregation? Thank you! -- João C. Haas
