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
);
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;
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
--
João C. Haas
