Multiple Aggregations Order

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