MySQL: Sortierung nach aktuellster ID bei GROUP BY

[mgutt]

Meine Abfrage (falsch, da man nicht group by sortieren kann):

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, ad.*
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al, phpbb_att_desc ad
WHERE t.forum_id = f.forum_id
AND p.post_id = al.post_id
AND al.attach_id = ad.attach_id
AND t.topic_id = p.topic_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC, al.attach_id DESC

Nun ist das Ergebnis egal wie ich herumteste immer so, dass die attach_id die älteste ist, die vorhanden ist und nicht wie ich es eigentlich möchte die aktuellste.

D.h. die Sortierung "al.attach_id DESC" greift nicht, da GROUP BY (wie auch HAVING) bereits selbst die Reihenfolge bestimmt, die ich zwar nachträglich beeinflussen kann, aber die dann nichts mehr enthält, was mich ans Ziel bringt.

Derzeit behelfe ich mich mit einer 2. Abfrage pro Forum, die nur die aktuellste attach_id liefert, aber das kann es ja nicht sein:

Code: Alles auswählen

SELECT ad.*
FROM phpbb_topics t, phpbb_posts p, phpbb_att_links al, phpbb_att_desc ad
WHERE t.forum_id = 1
AND t.topic_id = p.topic_id
AND t.topic_sub_type = 12
AND p.post_id = al.post_id
AND al.attach_id = ad.attach_id
ORDER BY p.post_id DESC
LIMIT 1

Ich weiß, dass es eine Lösung mit zwei verschachtelten Abfragen geben muss, die ich aber im Moment nicht herausfinde.

Weiterhin ist mir klar, dass ein Ergebnis aus GROUP BY nicht mehr nachträglich zu sortieren ist. Man muss bereits vorher die Reihenfolge festlegen. Vielleicht geht auch ein INNER JOIN, aber wie gesagt probiere ich rum, finde aber nicht den Richtigen heraus.

Übrigens geht MAX() im SELECT nicht, da dieser die Filterung aus WHERE nicht berücksichtigt und mir die höchste attach_id liefert, die die forum_id enthalten kann. Die Filterung des WHERE schließt aber bereits einige attach_ids aus, deshalb ist die MAX(attach_id) in der Regel falsch. Würde MAX() die WHERE Klauseln mit berücksichtigen wäre die Lösung leicht gewesen

Gruß

[Miriam]

Kannst Du mal bitte einen Dump der beteiligten Tabellen anhängen (mit ein paar Beispieldaten)?
Ich blicke gerade nicht, wieso Du COUNT() verwendest.

[mgutt]

Mit COUNT() zähle ich die Anzahl aller Attachments in einem Forum.

Ich möchte jedes Forum jeweils einmal haben (GROUP BY) inkl. der dort hochgeladenen Attachments (COUNT) und als letztes will ich das aktuellste Attachment aus diesem Forum ermitteln (ORDER BY attach_id DESC was nicht greift, da GROUP BY nicht nachträglich sortiert werden kann).

Ein Dump wird bei fünf Tabellen recht schwierig. Aber ich denke schon, dass man das nachvollziehen kann.

Es würde auch bei zwei Tabellen zu Problemen kommen:

forum_table:
forum_id | name
1 | name1
2 | name2

attach_table:
attach_id | forum_id
1 | 1
2 | 2
3 | 1
4 | 1
5 | 2

Würde man nun abfragen:

Code: Alles auswählen

SELECT f.forum_id, COUNT(a.attach_id) as count, a.attach_id
FROM forum_table f, attach_table a
WHERE f.forum_id = a.forum_id
GROUP BY f.forum_id

Das Ergebnis wären zwei Zeilen:
forum_id | count | attach_id
1 | 3 | 1
2 | 2 | 2

Ich will aber nicht die für forum_id 1 die attach_id 1 (also die älteste), sondern 4 (die aktuellste).

Und diese Abfrage hilft dabei leider nicht:

Code: Alles auswählen

SELECT f.forum_id, COUNT(a.attach_id) as count, a.attach_id
FROM forum_table f, attach_table a
WHERE f.forum_id = a.forum_id
GROUP BY f.forum_id
ORDER BY a.attach_id DESC

Eine Lösung, die greifen würde, wenn ich keine weiteren Filter gesetzt hätte, wäre MAX():

Code: Alles auswählen

SELECT f.forum_id, COUNT(a.attach_id) as count, MAX(a.attach_id) as max_id
FROM forum_table f, attach_table a
WHERE f.forum_id = a.forum_id
GROUP BY f.forum_id
ORDER BY a.attach_id DESC

In diesem Fall wäre das Ergebnis korrekt:
forum_id | count | max_id
1 | 3 | 4
2 | 2 | 5

Aber da ich nur die Attachments von ganz bestimmten Topics auslese greift dieser "Trick" leider nicht.

Gruß

[Pyramide]

Aber da ich nur die Attachments von ganz bestimmten Topics auslese greift dieser "Trick" leider nicht.

Also wenn der Wert aus count() korrekt ist, dann ist auch der von max() korrekt. Ausser du willst, daß z.B. count() die Anzahl im gesamten Forum ausgibt und max() nur von bestimmten Tabellen - dann musst du die Tabelle mit zwei Aliasen einbinden.

[mgutt]

Stimmt Du hast recht. Ich habe in der zweiten Abfrage nach post_id sortiert, weshalb es zu unterschiedlichen Ergebnissen kam. [ externes Bild ]

Ok, nun habe ich neueste ID, aber dummerweise fehlen mir jetzt die restlichen Daten zu der ID:

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, MAX(al.attach_id) as max_id, ad.*
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al, phpbb_att_desc ad
WHERE t.topic_id = p.topic_id
AND al.attach_id = ad.attach_id
AND p.post_id = al.post_id
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

"max_id" ist die ID des neuesten Bildes. Aber "ad.*" liefert die Daten aus dem GROUP BY.

Wie komme ich nun an die "ad.*" Daten in Verbindung zur "max_id"?

Die max_id kann ich ja nicht in der WHERE Klausel nutzen. (ich habe es getestet mit "AND max_id = ad.attach_id")

Derzeit behelfe ich mich wieder mit einer zwei Abfragen: (ist aber schon um einiges schneller geworden das ganze)

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, MAX(al.attach_id) as max_id
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

und dann:

Code: Alles auswählen

SELECT ad.*
FROM ' . ATT_DESC_TABLE . ' ad
WHERE ad.attach_id = ' . $row['max_id']

Ich habe ich auch schon was verschachteltes ausprobiert, aber die richtige Lösung will irgendwie nicht herauskommen

Gruß

P.S.:

Falles es jemanden interessiert. Man kann sich das Ergebnis hier anschauen:
http://www.maxrev.de/albumtest.php
Ziel ist es Smartors Album komplett zu entfernen, während das Album komplett auf den Attachment Mod aufbaut.

[easygo]

Derzeit behelfe ich mich wieder mit einer zwei Abfragen: (ist aber schon um einiges schneller geworden das ganze)

Erweitere deine erste SQL query um

, ' . ATT_DESC_TABLE . ' ad

+

AND ad.attach_id = al.attach_id

und probiers dann mal mit ner for Schleife.. macht deine Monsterabfrage
nicht unbedingt schneller, gibt aber deutlich weniger queries. easy

[mgutt]

Hi,

in der ersten Abfrage aus meinem letzten Beitrag siehst Du, dass ich das so hatte, aber die attach_id leider die älteste ist, die durch die WHERE/GROUP BY Klauseln ermittelt wird.

Oder habe ich jetzt etwas missverstanden?

Gruß
Marc

[easygo]

Wenn du meinst, dass sich das stört (ich kanns schlecht prüfen)

Setz als Beispiel die query vor die erste

Code: Alles auswählen

SELECT * FROM ' . ATT_DESC_TABLE;
$atrow = array();
while ( $row = $db->sql_fetchrow($result) )
{
	$atrow[] = $row;
}

und frag die ID dann in ner for Schleife ab. easy

[S2B]

Wie komme ich nun an die "ad.*" Daten in Verbindung zur "max_id"?

Mit einem Query der aktuellen Form (also mit JOINs) ist das imho nicht möglich. Das MySQL-Handbuch schlägt eine Unterabfrage vor:
http://dev.mysql.com/doc/refman/5.1/de/ ... p-row.html

[mgutt]

Wenn du meinst, dass sich das stört (ich kanns schlecht prüfen)

Setz als Beispiel die query vor die erste

Code: Alles auswählen

SELECT * FROM ' . ATT_DESC_TABLE;
$atrow = array();
while ( $row = $db->sql_fetchrow($result) )
{
	$atrow[] = $row;
}

und frag die ID dann in ner for Schleife ab. easy

Das wird bei 50.000 Attachments etwas schwierig

Selbst wenn ich nur nach subtype auslese wird das Array trotzdem immer größer werden. Ich denke mal ab 5000 Zeilen wird das ganze denke ich mal langsamer als jetzt werden.

Wie komme ich nun an die "ad.*" Daten in Verbindung zur "max_id"?

Mit einem Query der aktuellen Form (also mit JOINs) ist das imho nicht möglich. Das MySQL-Handbuch schlägt eine Unterabfrage vor:
http://dev.mysql.com/doc/refman/5.1/de/ ... p-row.html

Ich habe es mal so:

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, ad.*
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al
LEFT JOIN phpbb_att_desc ad ON ad.attach_id = (

SELECT MAX(al2.attach_id)
FROM phpbb_att_links al2
WHERE al.attach_id = al2.attach_id

)
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

und so probiert:

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, ad.*
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al, phpbb_att_desc ad
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND ad.attach_id = (

SELECT MAX(al2.attach_id)
FROM phpbb_att_links al2
WHERE al.attach_id = al2.attach_id

)
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

beide aber immer die älteste ad.attach_id als Resultat.