Vertrackte (?) Abfrage

[Tobias]

Hi,

ich habe eine kleine Datenbank (MySQL), die ein von mir geschriebenes Drupal-Modul (<- tut weiter nichts zur Sache ) abbildet. Die einzelnen Spalten der Tabellen können in der angehängten Grafik nachgeschaut werden.

Die Tabelle "message" enthält ein paar Informationen zu Nachrichten, die über das Modul verschickt werden können.
"message_recipient_user" und "message_recipient_role" enthalten Informationen zu den Empfängern der Nachricht - getrennt danach, ob ein einzelner User oder eine Rolle - also in der Regel eine Gruppe von Benutzern - angesprochen werden soll.
Und "message_tracker" schließlich speichert Daten darüber, welcher Benutzer welche Nachricht wann abgerufen hat.

Eine Nachricht kann vom Verfasser mit ein paar Attributen versehen werden, u. a. mit einem Ablaufdatum. Mit folgender Query besorge ich mir die Nachrichten, die an den aktuellen Benutzer (oder eine seiner Rollen) adressiert und noch nicht abgelaufen sind:

SELECT DISTINCT(message.nid) 
FROM message, message_recipient_user, message_recipient_role 
WHERE 
(message.vid = message_recipient_user.vid AND message_recipient_user.uid = ?) OR
(message.vid = message_recipient_role.vid AND (message_recipient_role.rid = ?)) AND
(expiration_date = '0000-00-00 00:00:00' OR expiration_date > NOW())

Der Part "message_recipient_role.rid = ?" wird pro Rolle des Benutzers wiederholt und mit OR verknüpft. "expiration_date = '0000-00-00 00:00:00'" ist eine Anpassung an Drupal, normalerweise sollte hier NULL eingetragen werden, weil dies Nachrichten OHNE Ablaufdatum markiert.

Nun zur eigentlichen Frage: Ich möchte obige Abfrage so erweitern, dass auch die Nachrichten zurückgegeben werden, die zwar abgelaufen sind, seit dem aber nicht mehr vom Benutzer eingesehen wurden. Zweck dieser Übung ist, dass der Benutzer nochmal darauf hingewiesen werden soll, dass eine Nachricht NICHT mehr gültig ist.

Meine Versuche gehen etwa in diese Richtung:

SELECT DISTINCT(message.nid) 
FROM message, message_recipient_user, message_recipient_role, message_tracker WHERE 
(message.vid = message_recipient_user.vid AND message_recipient_user.uid = ?) OR
(message.vid = message_recipient_role.vid AND (message_recipient_role.rid = ?)) AND
(expiration_date = '0000-00-00 00:00:00' OR expiration_date > NOW() OR
(DATEDIFF(expiration_date, MAX(message_tracker.time)) > 0 AND
message_tracker.nid = message.nid AND message_tracker.uid = ?))

"MAX(message_tracker.time)" sollte mir dabei die letzte Abrufzeit des Benutzers für diese Nachricht liefern, führt aber zu folgendem Fehler "user warning: Invalid use of group function query". Hat jemand mal einen Denkanstoß für mich?

mpG
Tobias

 

[musiKk]

Klar, Aggegierungsfunktionen wie max, min, avg etc. sind nur sinnvoll, wenn diese auf mehrere Datensätze angewendet werden. Welche Zeilen sollen bei Deinem Query aber verwendet werden? Alle? Nur die, auf die die letzte Zeile trifft? Das ist unklar. Daher können die nicht in der WHERE-Klausel auftreten. Ich bin jetzt nicht huntertprozentig reingestiegen, aber vielleicht hilft Dir dieses kleine Beispiel schon. Ein Subquery könnte schon das richtige sein.

 

[Tobias]

Mh, ok, ich sehe das Problem. Aber die Datensätze, die in die Berechnung von MAX(message_tracker.time) eingehen sollen, sind die der aktuell betrachteten Nachricht und des aktuellen Nutzers. Den Nutzer kann ich statisch festlegen, aber wie kriege ich den Subquery dazu, dieselbe Nachricht wie der Hauptquery zu betrachten?

SELECT DISTINCT(message.nid) 
FROM message, message_recipient_user, message_recipient_role, message_tracker WHERE 
(message.vid = message_recipient_user.vid AND message_recipient_user.uid = ?) OR
(message.vid = message_recipient_role.vid AND (message_recipient_role.rid = ?)) AND
(expiration_date = '0000-00-00 00:00:00' OR expiration_date > NOW() OR
(DATEDIFF(expiration_date, (SELECT MAX(message_tracker.time) 
FROM message_tracker 
WHERE message_tracker.nid = message.nid AND message_tracker.uid = ?)) > 0 AND
message_tracker.nid = message.nid AND message_tracker.uid = ?)

Wenn der oben eingefügte Subquery unabhängig von der umgebenden Abfrage ausgeführt wird, selektiert der mir doch immer die letzte Abrufzeit irgendeiner Nachricht - und nicht die der gerade vom Hauptquery betrachteten ...

mpG
Tobias

 

[sparrow]

Du musst innerhalb der Subquery die betrachteten Datensätze auf den von dir benötigten Bereich einschränken.
Also entsprechend auch mit Where definieren auf welchen Bereich du die max()-Funktion ausführen willst.

 

[Tobias]

Naja, das ist soweit klar, die Frage war mehr, ob Subqueries sich den Kontext mit dem umgebenden Query teilen oder nicht. Tun sie anscheinend, Frage ist also erledigt. Danke an alle Beteiligten!

mpG
Tobias