Kochrezept gesucht: Zählen von Einträgen von verknüpften Tabellen

[mssqler]

Hallo,

nehmen wir einmal an zwischen der Tabelle tabmodules und der Tabelle tabvk1 besteht eine 1:n Relation, dann kann ich, wenn ich tabmodules listen möchte, ganz einfach die Zahl der Verknüpfungen (also "n") mit COUNT ausgeben.

[highlight=sql]
SELECT
`modules`.* , COUNT(vk1.id) AS vk1count,
FROM `tabmodules` `modules`
LEFT JOIN `tabvk1` `vk1` ON `modules`.`id` = `vk1`.`moduleid`
GROUP BY modules.id
[/highlight]

Leider lässt sich diese Vorgehensweise nicht auf eine Tabelle tabvk2, zu der analago die gleiche 1:n-Relation besteht, ausweiten:

[highlight=sql]
SELECT
`modules`.* , COUNT(vk1.id) AS vk1count, COUNT(vk2.id) AS vk2count,
FROM `tabmodules` `modules`
LEFT JOIN `tabvk1` `vk1` ON `modules`.`id` = `vk1`.`moduleid`
LEFT JOIN `tabvk2` `vk2` ON `modules`.`id` = `vk2`.`moduleid`
GROUP BY modules.id
[/highlight]

vk1count und vk2count liefern nun beide das Produkt der beiden Einträge.

Mir ist klar, dass man an dieser Stelle wohl auf Subview-Konstrukte zurück greifen kann, aber geht das auch ohne Subview?

Tia, Stefan

[fanderlf]

Ich würde sagen das geht ohne SubQueries nicht, da immer wenn Du tabvk1 und tabvk2 über die modules.id joinst immer ein Produkt herauskommen wird. Über ein komplizierteres Konstrukt ist das vielleicht irgendwie möglich - meistens geht alles schon immer irgendwie. Die Frage ist nur: Warum keine Subqueries? Sollte es um Performance gehen ist das SQL Statement sowieso nicht aussagekräftig, dazu müsste man sich den Execution Plan anschauen. Es kann gut sein dass Du 2 komplett verschiedene SQL Statements hintippst die dasselbe Ergebnis haben und die Datenbank intern auch dasselbe macht. Die Datenbank musst ja das SQL Statement auch erstmal interpretieren und in diesem Zuge werden die Statements oft auch optimiert.

[akretschmer]

Leider lässt sich diese Vorgehensweise nicht auf eine Tabelle tabvk2, zu der analago die gleiche 1:n-Relation besteht, ausweiten:

[highlight=sql]
SELECT
`modules`.* , COUNT(vk1.id) AS vk1count, COUNT(vk2.id) AS vk2count,
FROM `tabmodules` `modules`
LEFT JOIN `tabvk1` `vk1` ON `modules`.`id` = `vk1`.`moduleid`
LEFT JOIN `tabvk2` `vk2` ON `modules`.`id` = `vk2`.`moduleid`
GROUP BY modules.id
[/highlight]

vk1count und vk2count liefern nun beide das Produkt der beiden Einträge.

Mir ist klar, dass man an dieser Stelle wohl auf Subview-Konstrukte zurück greifen kann, aber geht das auch ohne Subview?

Tia, Stefan

Ich denke mal, das ist ein Fehler von MySQL. Das geht schon los, daß Du modules.* abfragst, aber nur nach einer Spalte von modules gruppierst. Sehr wahrscheinlich hat modules mehr als eine Spalte, hier verstößt MySQL dann gegen die SQL-Spec, die besagt, daß alle Spalten im Resul entweder aggregiert oder gruppiert sein MÜSSEN.

test=*# select * from tabmodules ;
 id
----
  1
  2
  3
(3 rows)

Time: 0,185 ms
test=*# select * from vk1;
 fid
-----
   1
   1
   2
(3 rows)

Time: 0,106 ms
test=*# select * from vk2;
 fid
-----
   2
   2
   3
   3
   3
(5 rows)

Time: 0,081 ms
test=*# select m.id, count(vk1.fid), count(vk2.fid) from tabmodules m left join vk1 on m.id=vk1.fid left join vk2 on m.id=vk2.fid group by m.id;
 id | count | count
----+-------+-------
  1 |     2 |     0
  2 |     2 |     2
  3 |     0 |     3
(3 rows)

Time: 0,456 ms

Gewöhn Dir sauberes SQL an. Wenn Du auf der sicheren Seite sein willst, wirf MySQL weg. Es ist Müll.

[fanderlf]

Das habe ich gar nicht gesehen, aber das ist natürlich richtig. Du solltest im SELECT nicht modules.* verwenden wenn Du unten nur nach ID gruppierst. MySQL gruppiert vermutlich implizit einfach nach den anderen Spalten die noch in der modules Tabelle mit. Da kommt dann aber ein total falsches Ergebnis raus.

Dass man deswegen gleich MySQL verdammt halte ich für übertrieben. Es gibt genügend Projekte die gut auf einer MySQL DB laufen. Was wäre denn die Alternative?

[Falk Prüfer]

Hallo,

...hier verstößt MySQL dann gegen die SQL-Spec, die besagt, daß alle Spalten im Resul entweder aggregiert oder gruppiert sein MÜSSEN.

Diese "Spec" ist veraltet! Die aktuelle Definition besagt, dass im GROUP BY die Spalten weggelassen werden können, die aggregiert oder funktional abhängig sind. Sprich: Wenn zu jeder ID eindeutig nur genau ein Name existiert, dann brauch ich nur nach ID gruppieren, auch wenn der Name mit im SELECT steht! Da das Prüfen dieser funktionalen Abhängigkeit offensichtlich in keinem Verhältnis zum Nutzen steht, implementieren alle DBMS lediglich die alte - o.g. - Regel. Als einziges mir bekannte DBMS macht hier MySQL die Ausnahme und überlässt es dem Programmierer die funktionale Abhängigkeit einzuschätzen. Dieses Verhalten kann man übrigens über den SQL-Mode des Server beeinflussen. Man kann also MySQL durchaus so konfigurieren, dass es sich wie jedes andere DBMS verhält oder aber die "Features" nutzen - kein Grund es wegzuwerfen.

Gruß Falk

P.S.: Mals zum Lesen: Debunking GROUP BY myths

[Falk Prüfer]

...Mir ist klar, dass man an dieser Stelle wohl auf Subview-Konstrukte zurück greifen kann, aber geht das auch ohne Subview?

Wenn ich mal davon ausgehe, dass vk1.id und vk2.id jeweisl die Primärschlüssel der jeweiligen Tabellen sind, dann sollte man schon mit einem ...
[highlight=sql]
... COUNT(DISTINCT vk1.id) AS vk1count, COUNT(distinct vk2.id) AS vk2count, ...
[/highlight]
das gewünschte Ergebniss erziehlen lönnen.

Gruß Falk

[akretschmer]

Als einziges mir bekannte DBMS macht hier MySQL die Ausnahme und überlässt es dem Programmierer die funktionale Abhängigkeit einzuschätzen.

Nicht ganz richtig. PostgreSQL erlaubt es, wenn es erkennt, daß solch eine Abhängigkeit gegeben ist: ein Primary Key.

[akretschmer]

Dass man deswegen gleich MySQL verdammt halte ich für übertrieben. Was wäre denn die Alternative?

PostgreSQL?

[mssqler]

Vielen Dank für die ganzen Antworten und Anregungen.

Der Ansatz mit dem Einbau von DISTINCT in die COUNT-Anweisung klingt plausibel, ich kann das beim nächsten Aufspielen auf die Testumgebung ausprobieren.

1.) MySql ist in diesem Projekt nicht weg zu diskutieren, bisher sind wir da auch gut klar gekommen. Trotzdem: gerade im Zusammenhang mit Aggregierungsfunktionen habe ich bisher aber immer mal wieder einen Workaround gebraucht.
2.) Ich habe leider auch so gut wie keinen Einfluss auf die MySql-Konfiguration.