✔ [MySQL] ZUERST sortieren DANN gruppieren

n00ne

n00ne

Mitglied
Hallo,

ich habe eine Tabelle mit Kommentaren zu bestimmten Beiträgen, die nochmal in Unterbeiträge aufgegliedert sind.
Die Tabellenstruktur sieht sinngemäß ungefähr wie folgt aus:

Code: 
id,
bid,
bid2,
timestamp,
comment

Jetzt möchte ich im Endeffekt die Summe aller Kommentare, die zu einem bestimmten Unterbeitrag abgegeben wurden, zusammenzählen und zudem den aktuellsten Kommentar auslesen.

Mein SQL-Statement sieht zurzeit so aus:
Code: 
SELECT
  id,
  bid, 
  bid2,
  comment,
  `timestamp`,
  COUNT(*) anzahl
FROM 
  comments 
WHERE 
  bid = 1
GROUP BY 
  bid2
ORDER BY 
  `timestamp` DESC
Das funktioniert soweit auch. Ich erhalte wie gewünscht zu jedem Unterbeitrag, innerhalb des Hauptbeitrages (bid = 1), die Anzahl der gesammten Kommentare. Zudem bekomme ich selbstverständlich auch einen Kommentar geliefert. Dieser ist allerdings nicht der Aktuellste, sondern einfach nur der erste Kommentar, der gespeichet wurde.

Mir ist bewusst, dass MySQL erst gruppiert und danach sortiert. Allerdings wäre es schön, wenn ich diese Vorgehensweise irgendwie umgehen konnte. D.h. ich würde gerne zuerst die Kommentare nach ihrem timestamp sortieren und erst dann gruppieren.

PS
Ich könnte natürlich auch einfach die Daten ungruppiert auslesen. Jedoch würde ich dann eine Vielzahl von vollkommen unnützen Daten bekommen. Schließlich will ich auf einer Übersichtsseite nur den aktuellste Kommentar und alle insgesamt abgegebene Kommentare haben.

Vielen Dank schon im Voraus für die Hilfe,

André
 
Hi,

danke, die Sache mit dem LEFT JOIN funktioniert prinzipiell sehr gut, nur kann ich damit nicht gleichzeitig die Anzahl der Kommentare, die zu einem Unterbeitrag abgegeben wurden auslesen. Mit anderen Worten: Verwende ich GROUP BY zusammen mit diesem LEFTJOIN-Konstrukt, ist die Sortierung wieder hinfällig.
Nichtsdestotrotz kann ich damit schon - dank zweier MySQL-Queries - etwas anfangen.

Subqueries sind auch was feines, allerdings läuft auf vielen Shared-Servern noch eine ältere Version als 4.1. :(

In diesem Sinne: Vielen Dank!
 
Doch, Du kannst das in einer Abfrage machen. Ungefähr so müßte es funktionieren:
Code: 
SELECT
  t1.groupFeld,
  t1.feld1,
  t1.feld2,
  t1.feld3,
  t1.maxFeld,
  COUNT(t1.groupFeld) as Anzahl
FROM Tabelle as t1
LEFT JOIN Tabelle AS t2
  ON t2.groupFeld = t1.groupFeld
  AND t2.maxFeld > t1.maxFeld
JOIN Tabelle AS t3
  ON t3.groupFeld = t1.groupFeld
GROUP BY t1.groupFeld
WHERE t2.maxFeld IS NULL
Gruß hpvw
 
Hm,
bei dem Query blick ich jetzt irgendwie nichtmer ganz durch.
So wie ich das umgesetzt habe, funktioniert es auf jeden Fall nicht.

Hier mal meine "praxisnahe" Umsetzung:
Code: 
SELECT 
  f.fid, f.`time` zeit, f.uid, COUNT(f.fid) anzahl
FROM 
  foto_comments f
LEFT JOIN 
  foto_comments b ON f.fid = b.fid
  AND 
  f.time < b.time
JOIN 
  foto_comments c ON c.fid = f.fid
WHERE 
  b.cid IS NULL
  AND
  f.fid IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 )
  AND 
  f.cid = 47
GROUP BY
   f.fid

Zur Erklärung:
cid: Das ist die Id des Foto-Sets, also die ID des "Haupbeitrags"
fid: Steht für die Fotonummer (das 'Groupfeld')
time: Enthält den Unix-Timestamp (das 'Maxfeld')

Bei dieser Abfrage sollten eigentlich zwei Datensätze rauskommen.
Bei fid 4 gibt es einen Kommentar.
Bei fid 10 gibt es 2.

Ich bekomme hingegen nur einen Datensatz, bei dem die fid 4 ist und die Anzahl gleich 13 ist.

Sehr sonderbar....

Vielleicht hab ich auch nur gestern Nacht 'ne Kleinigkeit übersehen. Aber irgendwie seh ich da im Moment den Fehler nicht.
 
Ein paar Kleinigkeiten fallen mir auf:
Oben projezierst Du f.uid, unten arbeitest Du mit f.cid.
Du prüfst nicht das "max-Feld" auf NULL. Das dürfte egal sein, wichtig ist jedoch, dass das Feld, welches Du auf NULL prüfst als NOT NULL deklariert sein muss.
Du hast zum Teil das Schlüsselwort time nicht in Backticks gesetzt, obwohl es ein Feld anspricht.
Die Syntax, in der Projektion Aliasnamen ohne AS anzugeben war mir in MySQL noch nicht bekannt, aber da das Query keinen Fehler zu produzieren scheint, lerne ich gerne dazu.

Die Idee des Querys kurz erklärt:
Der erste JOIN und die Prüfung auf IS NULL soll das Ergebnis, wie in Toms Beispiel, auf die Maximalwerte reduzieren, der nächste JOIN soll die Tabelle wieder vollständig machen, damit man zählen kann.

Ohne Probieren stehe ich jetzt auch ein bisschen auf dem Schlauch. Vielleicht kannst Du ja einen Dump der Tabelle (Struktur und Daten, am besten mit PHPMyAdmin) der Tabelle posten. Dann könnte ich mal ein bisschen probieren. Ich bin mir sicher, dass es geht, ich wieß nur gerade nicht wie.

Gruß hpvw
 
Also:

Die uid enthält die ID des Kommentarschreibers, sie tut also eigentlich nichts zur Sache.
Die ungeschickte Verwendung des reservierten Wortes `time`resultiert aus dem Alter dieser Tabelle. (Sie gehört zu einer meiner ersten Tabellen). Normal setz ich da immer Backticks, ging nur gestern alles etwas schnell.
Genauso hab ich gestern auch aus Versehen mal das "falsche" Feld auf NULL geprüft.
Leider liefter dieser Query das Gleiche Ergebnis wie vorher:
Code: 
SELECT f.fid, f.`time` zeit, f.cid, COUNT(f.fid) anzahl
FROM foto_comments f
LEFT JOIN foto_comments b ON f.fid = b.fid
AND f.`time` < b.`time`
JOIN foto_comments c
ON c.fid = f.fid
WHERE b.`time` IS NULL
AND f.fid
IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 )
AND f.cid =47
GROUP BY f.fid

Die Struktur meiner Tabelle sowie einige Beispielsdatensätze findest du im Anhang.

Kurze Erläuterung zu den Feldern:
id: PK
cid: ID des Fotosets
fid: ID/Nummer des Fotos
time: UNIX TIMESTAMP
uid: Die ID des Users
kommentar: der kommentar
[uid kann auch einen string enthalten, deswegen varchar ;) ]

Im Übrigen stell ich gerade fest, dass ich die Tabelle vielleicht doch mal etwas überarbeiten sollte... Aber das nur am Rande als Notiz an meine Wenigkeit :)

Vielen Dank nochmals für die Hilfe!

PS
Ja, MySQL benötigt nicht zwingend das Wörtchen AS um Aliase zu definieren.
 

Anhänge

  • comments.txt
    2,3 KB · Aufrufe: 45
Das Problem liegt daran, dass der jüngste Kommentar zur fid 10 nicht die cid 47 hat.

Es hat den Anschein, dass
  • verschiedene Fotos dieselbe fid haben können, wenn sie in verschiedenen Fotosets sind oder
  • gleiche Fotos in verschiedenen Sets nicht dieselben Kommentare haben sollen.
Sollte a zutreffen, solltest Du Dein Datenbankdesign überdenken.
Sollte b zutreffen, würde ich mir überlegen, ob es sinnvoll ist, Kommentare zu demselben Foto zu trennen, nur weil es in verschiedenen Kategorien auftaucht.
Vielleicht kannst Du mir ja auch noch "c" nahelegen, so dass alles einen anderen Sinn ergibt.

Auf jeden Fall ist Dein Gruppierungs-Feld nicht nur die fid, sondern sowohl fid, als auch cid.

Folgendes Query führt zu dem Ergebnis, welches ich nach Deinen bisherigen Ausführungen erwarte:
Code: 
SELECT 
  f.fid, 
  f.`time` zeit, 
  f.cid, 
  COUNT(f.fid) anzahl
FROM foto_comments f
LEFT JOIN foto_comments b 
  ON f.fid = b.fid
    AND f.cid = b.cid
    AND f.`time` < b.`time`
JOIN foto_comments c
  ON c.fid = f.fid
    AND f.cid = c.cid
WHERE b.`time` IS NULL
  AND f.fid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
  AND f.cid =47
GROUP BY f.fid, f.cid
Ergebnis:
Code: 
fid   zeit         cid   anzahl
 4    1093181295    47    1
10    1093281171    47    2
Gruß hpvw
 
Die cid steht im Endeffekt für eine Fotostrecke.

D.h. Alle Fotos mit der cid 47 sind z.B. Urlaubsfotos von diesem Sommer. Fotos mit der cid 50 sind Fotos von der letzten Feier.
Insofern kommen Fotos (fids) auch doppelt vor. Fotos mit einer anderen cid liegen auch in einem anderen Ordner und sind jeweils fortlaufend von 1-n durchnummeriert.
Somit trifft dein Punkt a zu.

Ich wüsste in diesem Punkt auch nicht, wie ich das anders lösen sollte.
Die Kombination cid = 47 und fid = 10 ist schließlich eindeutig einem Foto zugeordnet. (Foto Nummer 10 von den "Urlaubsfotos")
Die Tabelle, die die eigentlich Fotosets enthält sieht wieder etwas anders aus.

Dein Query bau ich gleich ein. Vielen Dank nochmals!

[edit]
Funktioniert wunderbar. ;-)
[/edit]
 
Zuletzt bearbeitet:
Mein DB-Design würde ungefähr so aussehen (ich hoffe, es ist ohne Kommentare verständlich):
Tabelle Foto
ID, int pk auto increment
Fotodatei, char (Der Dateiname, unter dem das Foto im Dateisystem zu finden ist)
Beschreibung, char
Fotograf, char
usw.

Tabelle Kategorie
ID, int pk auto increment
Name, char
Beschreibung, char
ggf. parentKategorieID, int fk (für Unterkategorie)

Tabelle FotoInKategorie
FotoID, int fk (Teil des pk)
KategorieID, int fk (Teil des pk)
ggf. Position (um eine Reihenfolge zu definieren)

Jetzt alternativ:
entweder (Kommentare zu einem Foto sind immer gemeinsam, egal in welcher Kategorie das Foto gerade liegt):
Tabelle Kommentare
ID, int pk auto increment
FotoID, int fk
Kommentar, text
Autor, int fk (oder char, wenn keine festen User Kommentare schreiben)
Zeitpunkt, datetime

oder (Kommentare in einer Kategorie, tauchen bei dem gleichen Foto nicht in einer anderen Kategorie auf):
Tabelle Kommentare
ID, int pk auto increment
FotoID, int fk
KategorieID, int fk
Kommentar, text
Autor, int fk (oder char, wenn keine festen User Kommentare schreiben)
Zeitpunkt, datetime

Bei Dir gibt es entweder keine eigene Tabelle für Fotos oder die Fotos lassen sich in der Fototabelle nicht eindeutig identifizieren oder dasselbe Foto taucht mehrfach in der Fototabelle auf, weil es in mehreren Kategorien dargestellt wird.

Wie Du es ohne Foto-Tabelle einrichten würdest, könnte ich mir nicht erklären. Einen Datensatz nicht eindeutig identifizieren zu können bereitet sicherlich einige Probleme. Das gleiche Foto mehrfach in der Tabelle zu erwähnen wiederspricht der Normalisierung.

Vielleicht gibt es aber auch eine vierte Variante der Modellierung, die ich übersehen habe und die dennoch sinnvoll ist, obwohl die Fotonummern mehrfach vergeben sind.

Gruß hpvw
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück