Multiple Aggregations Order

--0000000000003ec726059c1ce5df
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D 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!
--=20
Jo=C3=A3o C. Haas

--0000000000003ec726059c1ce5df
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><font face=3D"monospace">Hi there,<br><br>I&#39;m working =
on a query where I need to fetch information from a table along with some d=
ata from a many-to-many connection table in a single query. My idea is to d=
o an outer join with the connection query and aggregate the needed data in =
multiple &#39;array_agg&#39;s, and then handle this aggregated data later i=
n code.<br><br>The issue is, there are a lot of aggs=C2=A0(4 by now, may in=
crease later), and I need to order these by a &#39;order&#39; field on the =
connection table. I can put an &#39;ORDER BY &quot;order&quot;&#39; stateme=
nt inside each &#39;array_agg&#39;, but I don&#39;t think that would be the=
 most efficient way. Doing the join with a sorted connection table didn&#39=
;t work for me as well,=C2=A0probably due to other joins on the query. I tr=
ied doing some stuff with subqueries, but all attempts ended up in either f=
ailure or increased query time.<br><br>The structure is a graph based struc=
ture, and both query and structure themselves are quite complex, but a simp=
lified example would look like this:<br><br>CREATE TABLE tb (<br><span styl=
e=3D"color:rgb(13,10,11);font-size:10.7333px;white-space:pre-wrap">    </sp=
an>id SERIAL PRIMARY KEY<br>);<br>CREATE TABLE conn (<br><span style=3D"col=
or:rgb(13,10,11);font-size:10.7333px;white-space:pre-wrap">    </span>paren=
t_id INTEGER,<br><span style=3D"color:rgb(13,10,11);font-size:10.7333px;whi=
te-space:pre-wrap">    </span>child_id INTEGER,<br><span style=3D"color:rgb=
(13,10,11);font-size:10.7333px;white-space:pre-wrap">    &quot;</span>order=
&quot; INTEGER,<br><span style=3D"color:rgb(13,10,11);font-size:10.7333px;w=
hite-space:pre-wrap">    </span>kind INTEGER<br>);</font><div><font face=3D=
"monospace"><br></font></div><div><font face=3D"monospace">INSERT INTO tb V=
ALUES (1), (2), (3);</font></div><div><font face=3D"monospace">INSERT INTO =
conn VALUES (1, 2, 2, 10), (1, 3, 1, 20);<br><br>SELECT tb.*, array_agg(con=
n.child_id), array_agg(conn.kind)<br>FROM tb<br>LEFT OUTER JOIN conn<br>ON =
conn.parent_id =3D <a href=3D"http://tb.id">tb.id</a><br>GROUP BY <a href=
=3D"http://tb.id">tb.id</a>;</font></div><div><div><font face=3D"monospace"=
><br></font></div><div><font face=3D"monospace">Not sure if there&#39;s rea=
lly a solution, but does anyone have any idea on how to solve this issue wi=
thout doing multiple &#39;ORDER BY&#39;s=C2=A0inside each aggregation?</fon=
t></div><div><font face=3D"monospace"><br></font></div><div><font face=3D"m=
onospace">Thank you!<br>-- <br>Jo=C3=A3o C. Haas</font></div></div></div>

--0000000000003ec726059c1ce5df--