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:
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?