Aggregatfunktionen für mehrere verknüpfte Tabellen
Ich habe drei Tische:
CREATE TABLE foo (
id bigint PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE foo_bar (
id bigint PRIMARY KEY,
foo_id bigint NOT NULL
);
CREATE TABLE tag (
name text NOT NULL,
target_id bigint NOT NULL,
PRIMARY KEY (name, target_id)
);
Ich versuche, eine Ansicht so zu erstellen, dass ich alle Felder der Tabelle erhaltefoo
, die Anzahl der Artikel infoo_bar
woherfoo.id = foo_bar.foo_id
und ein Textfeld mit allen Tags, in denenfoo.id = tag.target_id
. Wenn wir haben:
INSERT INTO foo VALUES (1, 'one');
INSERT INTO foo VALUES (2, 'two');
INSERT INTO foo_bar VALUES (1, 1);
INSERT INTO foo_bar VALUES (2, 1);
INSERT INTO foo_bar VALUES (3, 2);
INSERT INTO foo_bar VALUES (4, 1);
INSERT INTO foo_bar VALUES (5, 2);
INSERT INTO tag VALUES ('a', 1);
INSERT INTO tag VALUES ('b', 1);
INSERT INTO tag VALUES ('c', 2);
Das Ergebnis sollte zurückgeben:
foo.id | foo.name | count | array_agg
--------------------------------------------------
1 | one | 3 | {a, b}
2 | two | 2 | {c}
Das habe ich bisher:
SELECT DISTINCT f.id, f.name, COUNT(b.id), array_agg(t.name)
FROM foo AS f, foo_bar AS b, tag AS t
WHERE f.id = t.target_id AND f.id = b.foo_id
GROUP BY f.id, b.id;
Dies sind die Ergebnisse, die ich erhalte (beachten Sie diecount
ist falsch):
foo.id | foo.name | count | array_agg
--------------------------------------------------
1 | one | 2 | {a, b}
2 | two | 1 | {c}
Dascount
ist immer die Anzahl der Tags anstelle der Anzahl der eindeutigenfoo_bar
Werte. Ich habe versucht, das neu zu ordnen / zu ändernGROUP BY
und dasSELECT
Klauseln, die unterschiedliche Ergebnisse liefern, aber nicht die, nach denen ich suche. Ich glaube, ich habe Probleme mit demarray_agg()
Funktion, aber ich bin nicht sicher, ob das der Fall ist oder wie man es löst.