SQL Verbesserungsvorschläge und Frage zu Query mit JOINs

[jaegerschnitzel]

Ich will eine Liste mit meinen News ausgeben lassen. Zuerst habe ich mir ein Array mit allen News von der DB geholt und alle News in einer Schleife ausgeben lassen. Dann habe ich in der Schleife zu jeder News überprüft ob Kommentare existieren und wenn ja die Anzahl ausgeben lassen.

Nun wollte ich aber alles in einer Abfrage machen, da dies bei vielen News sicherlich schneller geht und auch DB-schonender ist.

Folgendes SQL-Statement habe ich nun entwickelt, welches auch funktioniert:

"SELECT n.id, n.id_user, n.datum, n.titel, n.nachricht, n.kommentar, cat.name, COUNT(c.id_news) AS c_count
FROM _news n
LEFT JOIN _comments c ON n.id = c.id_news
GROUP BY n.id
ORDER BY n.datum DESC"

Wollte aber fragen, ob es da ne bessere Möglichkeit gibt.

Meine 2te Frage bezieht sich auf die Kategorien.
Da ich mehrere Kategorien pro News ermögliche, habe ich die Zwischentabelle _news_category erstellt.
Nun bekomme ich die Abfrage aber nicht mehr richtig hin. Die Anzahl der Kommentare stimmt nicht mehr und auch die Namen der Kategorien werden nicht richtig ausgelesen.

Wenn z.B. zwei Kategorien pro News vorliegen sollten beide ausgegeben werden. Das lässt sich wohl nur mit einer Unterabfrage lösen oder? Falls ja, wie genau?

 

[ag3nt]

Morgen,
zuerst einmal ist dein Query nach ANSI-SQL falsch und wird auf vielen DBMS nicht ausgeführt (nur wenige DBMS wie MySQL akzeptieren solche Anfragen)!

Zu den Verbesserungen:

1. Wenn eine "News" mehr als eine Kategorie haben soll ist deine Tabelle "_news_category" falsch! Der PK muss über id_news und id_category gehen, id ist völlig überflüssig und bringt nichts.
2. Grundsätzlich wie immer: nur das anfragen was du wirklich brauchst!
3. Die Kategorien kannst du dir mit "normalen" SQL-Befehlen nicht so ausgeben lassen wie du das willst (zumindest nicht in einem Tabellenfeld)! Dazu bräuchtest du eine "String-Aggregation" aber nicht alle Datenbanken können das (ka ob MySQL dazu gehört).
   Möglichkeiten wären dann: du bastelst in der Anwendung das Abfrageergebnis um oder du stellst 2 verschieden Abfragen und fügst das Ergebnis zusammen. Die Aggregation musst du aber vermutlich in der Anwendung durchführen, von daher ist die Lösung mit den 2 Abfragen vermutlich die bessere.

-- so sollte es eher aussehen (cat.name kannst du natürlich nicht benutzen!)
SELECT 
       n.id
  ,    n.id_user
  ,    n.datum
  ,    n.titel
  ,    n.nachricht
  ,    n.kommentar
  ,    COUNT(c.id) AS c_count -- besser so!

FROM   _news n LEFT JOIN _comments AS c ON n.id = c.id_news 

GROUP BY  
       n.id
  ,    n.id_user
  ,    n.datum
  ,    n.titel
  ,    n.nachricht
  ,    n.kommentar

ORDER BY n.datum DESC


--eine Alternative, die vermutlich schneller ist
SELECT 
       n.id
  ,    n.id_user
  ,    n.datum
  ,    n.titel
  ,    n.nachricht
  ,    n.kommentar
  ,    ISNULL( c.c_count, 0 ) AS c_count

FROM   _news n LEFT JOIN ( 
		-- ein unique index über ( id_news, id ) [in dieser Reihenfolge]
		--oder ein berechneter index über (id_news, count(id) )
		--kann die abfrage enorm beschleunigen!

		SELECT id_news, COUNT(id) AS c_count
		FROM   _comments
		GROUP BY id_news
	) AS c

	ON n.id = c.id_news 

ORDER BY n.datum DESC


--Alternative 2:
-- du fügst zur newstabelle einen commentcounter hinzu (aktualisiert zb über trigger)
-- und erstellst einen absteigend sortiereten index auf datum

SELECT 
       id
  ,    id_user
  ,    datum
  ,    titel
  ,    nachricht
  ,    kommentar
  ,    kommentar_anzahl

FROM   _news

ORDER BY datum DESC

 

[jaegerschnitzel]

Morgen,
zuerst einmal ist dein Query nach ANSI-SQL falsch und wird auf vielen DBMS nicht ausgeführt (nur wenige DBMS wie MySQL akzeptieren solche Anfragen)!

Zu den Verbesserungen:

1. Wenn eine "News" mehr als eine Kategorie haben soll ist deine Tabelle "_news_category" falsch! Der PK muss über id_news und id_category gehen, id ist völlig überflüssig und bringt nichts.

Danke soweit mal.
Falsch ist das Query nur, weil ich nicht alle Spalten aus dem Select in die GROUP BY Klausel geschrieben habe oder?

Da zwischen News und Kategorie eine N:M Beziehung besteht muss ich eine Zwischentabelle benutzen zum Auflösen. Habs in der Schule auch so gelernt. Natürlich ist id als PK überflüssig aber nicht wirklich falsch.
Wie kann ich denn einen zusammengesetzten PK in MySQL verwenden?


Und mit der Alternative 1 komme ich nicht wirklich zurecht:

Incorrect parameter count in the call to native function 'ISNULL'

Ist eigentlich klar, da kein "c.c_count" existiert. Weiß aber nicht wie es richtig lauten muss.

 

[ag3nt]

wegen dem primärschlüssel:

create table test (
	a int , 
	b int,

	constraint pk_test primary key( a, b)
)

ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (a,b);

natürlich ist es nicht falsch in dem sinne, dass es ein fehler ist, aber da der schlüssel keine funktion hat und die anderen beiden felder ohnehin in unique sein müssen, ist es verschwendung (nicht alles was in der schule erzählt wird ist zu 100% korrekt und in freier wildbahn sowieso ).


wegen dem group:
ja, alle spalten die in der projektion (select) verwendet werden und nicht innerhalb einer aggrgatsfunktion stehen müssen in der group by klausel aufgeführt werden

zu deiner kategorie (alle beschreibungen in einem feld!):
nein, das lässt sich überhaupt nicht lösen! in mssql gibt es dafür eine art hack, aber in MySQL ist mir da keinerlei möglichkeit bekannt

zu der fehlermeldung:
doch das ist so korrekt, aber das unerstützt nicht jedes DBMS, da du aber nicht gesag hast was du nutzt, kann ich das natürlich auch nicht berücksichtigen

 

[jaegerschnitzel]

ich benutze MySQL 5.1.30

Bisher habe ich das Array mit den News immer ausgegeben und in der foreach-Schleife zu jeder News die Kategorien extra ausgelesen. Das wollte ich jetzt verbessern da dies keine optimale Lösung ist.

Dann werd ichs wohl mit einer 2ten Abfrage versuchen.

 

[ag3nt]

Das würde ich so machen:

1. Die news abfragen wie in den Beispielen.
2. Eine Abfrage schreiben die alle Kategorien mit dazu passender news_id liefert.

SELECT c.name, n.id_news
FROM _news_category AS n INNER JOIN _category AS c ON c.id = n.id_category
ORDER BY n.id_news, c.name

Das Ergbnis nachbearbeiten und in eine Form wie die bringen (z.B. Hashtable/Hashmap):

$catDesc = array(  $id_news => "cat1, cat, 2cat3 ...." );... //tippfehler sollte " => " und nicht ","

3. In der Ausgabe kannst du dann das Erbnis der Kategoren so mit ausgben:

while(...) {
    ...
   $catDesc[ $current_id_news ];
    ...
}

So würdest du mit 2 Abfragen auskommen

 

[jaegerschnitzel]

Herzlichen dank, so werde ich es angehen!

Deutlich besser als in der Schleife für jede News ne extra Abfrage zu machen

 

[jaegerschnitzel]

Jetzt muss ich doch nochmal nachfragen.

Komme nicht wirklich mit dem Erstellen der Hashtable zurecht. Meine Datenbankklasse erstellt nämlich aus einer gegebenen Abfrage automatisch ein Array. Dieses Array bekomme ich jetzt nicht wirklich schnell in die passende Form. Nur durch eine erneute Schleife. Zudem müsste jeder Kategoriename noch verlinkt werden.
Vielleicht kennst du dich da besser aus.

Folgendes Array bekomme ich beispielsweise zurück:

Array
(
    [0] => Array
        (
            [name] => Page
            [id_news] => 1
        )

    [1] => Array
        (
            [name] => News
            [id_news] => 3
        )

    [2] => Array
        (
            [name] => Page
            [id_news] => 3
        )

)

 

[ag3nt]

Array
(
    [0] => Array
        (
            [name] => Page
            [id_news] => 1
        )

    [1] => Array
        (
            [name] => News
            [id_news] => 3
        )

    [2] => Array
        (
            [name] => Page
            [id_news] => 3
        )

)

ist einfach am besten irgendwie so nach dem schema (nicht unbedingt mit so viel oberhead aber das ist ja erstmal egal):

$array_new = array();

foreach($array_old as $row) {
    $array_new[ $row['id_news'] ][] = $row['name'];
}

foreach($array_new as $key => $val) {
    $array_new[$key] = implode( ", " , $val );
}

 

[jaegerschnitzel]

Ok nun passt alles!
Danke nochmal.

Habe aber noch eine, hoffentlich letzte Frage
Wenn ich sagen wir 500 News habe und jede davon 3 Kategorien, dann existieren 1500 Einträge in der Tabelle "_news_category".
Wenn ich nur 10 News ausgeben lasse dauert das ganze Array-Umbauen sicher länger wie wenn ich für jede News die Kategorie extra hole. Was denkst du?

 

[ag3nt]

du musst ja nicht immer alle ausgeben sondern kannst dich auf die benötigten beschränken

 

[jaegerschnitzel]

also noch ne where klausel in das SQL-statement zum Kategorien holen einbauen?