GROUP BY Problem

[skalu]

Hi Leute,

ich habe folgendes Problem: in einer Tabelle "activities" speichere ich für jeden angemeldeten Benutzer seine Session + User-ID + letzte Aktivität. Nun möchte ich mir eine Liste ausgeben lassen, wer gerade aktiv ist. Das Problem ist nur, dass wenn sich ein User einloggt, dann seine Session nicht per Logout sondern über Schließen des Browserfensters beendet und sich dann binnen einiger Minuten nochmal einloggt, wird er von meinem Script so gesehen, als wäre er 2x eingeloggt, weil beide "letzte Aktivitäten" innerhalb des von mir festgelegten Timeouts von 10 Minuten liegen.

Gebe ich mir nun alle Aktivitäten der letzten 10 Minuten aus, erhalten ich den Benutzer, der sich 2x hintereinander eingeloggt hat, 2x angezeigt. Nun zum Problem: dem möchte ich per GROUP BY user_id entgegen wirken, jedoch erhalte ich dann immer den ältesten Datensatz und nicht den aktuellsten. Eine Kombination mit ORDER BY last_activity ändert an diesem Problem nichts.

Wie kann ich GROUP BY so einsetzen, dass ich jeden Benutzer nur 1x angezeigt bekomme und dann nur den neusten Datensatz?

Struktur + Beispieleinträge
activity_id | user_id | session_id | last_activity
---------------------------------------------
1 | 1 | id1 | timestamp1 (id1 und timestamp sind hier nur angedeutet)
2 | 5 | id2 | timestamp2
3 | 7 | id3 | timestamp3
4 | 5 | id4 | timestamp4 (neuester Eintrag)

>> group ich nun by user_id, erhalte ich als letzte Aktivität des Users "5" timestamp2 und nicht den neueren timestamp4. Hoffe ich konnte das gut erklären...

Danke, Skalu

[AmicaNoctis]

Hallo,

du musst natürlich

ORDER BY last_activity DESC

angeben.

Gruß,

Amica

[skalu]

Hi Amica,

ich habe in meinem 1. Beitrag vergessen, das DESC hinzuschreiben, aber die ORDER BY Klausel habe ich schon genauso probiert, wie du. Bringt leider nichts

Skalu

[AmicaNoctis]

Hm, dann bleibt dir immer noch der negative self left join. Siehe hier: http://www.php-resource.de/forum/sql...arte-nuss.html

[skalu]

Hi Amica,

danke für deine Antwort! Die Idee mit dem JOIN klingt ja sehr gut Ich habe mir deinen Link angesehen und mein Query mal so umgebaut, wie in deinem Beispiel. Leider verstehe ich einen Teil nicht bzw. denke ich, dass ich etwas falsch mache, weil das Query noch nicht so funktioniert, wie ich es brauche (ich bekomme nun ungefiltert alle Einträge, als ob ich kein GROUP BY verwenden würde.

SELECT * FROM user_activities t1 LEFT JOIN user_activities t2 
ON t1.activity_id = t2.activity_id AND t2.last_activity < t1.last_activity
WHERE t1.last_activity > '$timeout_ts'
ORDER BY t1.activity_id DESC

$timeout_ts ist der aktuelle TS minus meinem Timeout (- 10 min).

Kannst du mir vielleicht kurz erklären, was ich bei dem negativen JOIN theoretisch machen muss? Der Teil "AND t2.last_activity < t1.last_activity" scheint der Teil zu sein, der die Daten so selektiert, dass die neueren Datensätze gewählt werden.

Danke dir für deine Hilfe!

Skalu

[AmicaNoctis]

Im ON-Teil musst du den Vergleich umkehren (also t2.last_activity > t1.last_activity) und dann fehlt in der Where-Klausel noch das

AND t2.activity_id IS NULL

Das GROUP BY dagegen fliegt raus.

Die umgekehrte Bedingung bei ON und das IS NULL in der Where-Klausel machen das negative am negativen join aus.

[skalu]

Hi Amica,

tut mir Leid, dass sich das so zieht, aber es funktioniert leider noch immer nicht Hier mal das Query, das ich mir im Script habe ausgeben lassen:

SELECT * FROM user_activities t1 LEFT JOIN user_activities t2 
ON t1.activity_id = t2.activity_id AND t2.last_activity > t1.last_activity 
WHERE t1.last_activity >=1294480114 AND t1.activity_logged_out = 0 
AND t2.activity_id IS NULL ORDER BY t1.activity_id DESC

activity_logged_out ist bei allen Einträgen 0, spielt also keine Rolle und auch ohne diese Bedingung funktioniert es nicht. Leider bekomme ich noch immer Benutzer 2x angezeigt...

Kann da noch etwas falsch sein/fehlen?

Wenn ich das Query direkt in PHPMyAdmin eingebe, bekomme ich für alle Felder von t2 den Wert NULL. In der WHERE Bedingung frage ich das ja ab - ist das so in Ordnung, dass alle Felder von t2 den Wert NULL haben? Ich schreibe das nur, falls das eine Fehlerquelle sein könnte...

Danke vielmals für deine Bemühungen!

Skalu

[AmicaNoctis]

Ich kenne deine DB leider nicht, daher kann ich nicht viel dazu sagen.

Folgender Vorschlag: Poste das Ergebnis der Abfrage ohne die Where-Klausel (mind. 20 Datensätze):

SELECT *
FROM user_activities AS t1
LEFT JOIN user_activities AS t2 
    ON t1.activity_id = t2.activity_id AND t2.last_activity > t1.last_activity 
ORDER BY t1.activity_id DESC

Schreib dann dazu, welche Datensätze du nicht haben willst und warum.

[skalu]

Hi Amica,

danke für deine Mühe - los geht's

Also wenn ich keine WHERE Bedingung habe, erhalte ich ein solches Ergebnis ("[session_id]" enthält die jeweilige SESSION-iD (z.B. 4jal32k3jaskdj usw)):

activity_id | activity_user | activity_session_id | last_activity | ...
----------------------------------------------------------------
1 | 1 | [session_id ] | [alter Timestamp]
2 | 4 | [session_id ] | [neuerer Timestamp]
3 | 6 | [session_id ] | [noch neuerer Timestamp]
4 | 4 | [session_id ] | [noch neuerer Timestamp] <-- dieser Timestamp liegt beispielsweise nicht in den letzten 10 Min -> ist also schon uninteressant
5 | 1 | [session_id ] | [noch neuerer Timestamp] <-- dieser Timestamp von Benutzer "1" ist älter, als jener von Benutzer "1" ganz unten (Acitivity-ID 8) - diesen will ich somit nicht berücksichtigen, obwohl er in die letzten 10 Minuten fällt
6 | 7 | [session_id ] | [noch neuerer Timestamp] <-- will ich berücksichtigen, weil der neueste von Benutzer "7"
7 | 4 | [session_id ] | [noch neuerer Timestamp] <-- will ich auch berücksichtigen, weil neuester von Benutzer "4"
8 | 1 | [session_id ] | [neuester Timestamp] <-- diesen Eintrag von Benutzer "1" will ich schon berücksichtigen, weil er ist der neueste von Benutzer "1"

Sind jetzt nur 8 Ergebnisse - denke das reicht auch, um das Problem zu erklären: 8 User haben sich insgesamt eingeloggt.
- Die ersten 4 Einträge hatten ihre letzte Aktivität vor über 10 Minuten -> uninteressant, weil schon zu alt.
- Der Eintrag 5 ist zwar von Benutzer 1, es gibt aber noch neuere, deswegen auch uninteressant.
- Eintrag 6 von Benutzer 7 ist der einzige von Benutzer 7 -> es gibt keinen neueren -> will ich berücksichtigen
- Eintrag 7 ist der 3. von Benutzer 4, aber der neueste (die beiden älteren sind also uninteressant)
- Eintrag 8 ist der neueste von Benutzer 1 -> somit will ich nur diesen von Benutzer 1 anzeigen.

Es geht also darum, von jedem Benutzer immer nur den neuesten Eintrag anhand von "activity_timestamp" zu ermitteln, aber jeden Benutzer immer nur 1x anzuzeigen und dann nur den neuesten Eintrag in der Tabelle. Ich bekomme im Ergebnis also 4 Einträge, die in die letzten 10 Minuten fallen (Einträge 5-8 = fett markiert). In diesen 4 kommt aber ein Benutzer 2x vor (weil er sich nicht ausgeloggt, sondern nur sein Fenster geschlossen hat und sich ein 2. Mal eingeloggt hat, dennoch innerhalb von 10 Minuten). Diesen Benutzer will ich aber nur 1x angezeigt bekommen und zwar nur dessen neuesten Eintrag = neueste Aktivität. Der 2. (ältere) Eintrag dieses Benutzers ist nicht mehr relevant.

Hoffe ich konnte das nun besser erklären!

Danke, Skalu

[AmicaNoctis]

Achsoooo, dann nimm nochmal die Abfrage von hier und ersetze in der ON-Klausel activity_id durch activity_user.

[skalu]

Amica, du bist ein Genie Vielen Dank - jetzt funktioniert es endlich so, wie ich's wollte!! Danke für deine Geduld!!!

Skalu