MySQL-Abfrage: 3 Tabellen verknüpfen

[Seether]

Hallo,

ich möchte folgende 3 Tabellen verknüpfen:
phpbb_users u
phpbb_topics t
phpbb_posts p

Auslesen möchte ich lediglich den Usernamen.

Und zwar stelle ich mir das ganze so vor:
-Die Daten werden nach p.post_time sortiert
-Ein Datensatz wird nur dann selektiert wenn p.poster_id = t.topic_poster

Auf Deutsch:
Ich möchte den Usernamen von demjenigen, der in SEINEM Thema zuletzt geschrieben hat, haben.

Stehe gerade auf dem Schlauch und erhalte nicht die richtige Abfrage.

Mein letzter (falscher) Versuch:

Code: Alles auswählen

$sql = "SELECT u.username,p.post_id
        FROM (".USERS_TABLE." u
        INNER JOIN ".TOPICS_TABLE." t
        ON u.user_id = t.topic_poster)
        INNER JOIN ".POSTS_TABLE." p
        ON t.topic_poster = p.poster_id
        WHERE (p.forum_id = '1')
        GROUP by p.poster_id
        ORDER BY p.post_time DESC
        LIMIT 5";

S.

[gn#36]

Ich würde das so versuchen (nicht getestet und ohne Prefixe):

Code: Alles auswählen

SELECT u.username
FROM users u
LEFT JOIN topics t ON u.user_id = t.poster_id
LEFT JOIN posts p ON t.poster_id = p.poster_id AND p.post_id = t.last_post_id
WHERE p.post_id IS NOT NULL AND t.forum_id = 1
ORDER BY p.post_time DESC

[Seether]

AND p.post_id = t.last_post_id

Also diese Bedingung ist nicht nötig bzw. falsch bei dem was ich suche. Der Beitrag muss nicht unbedingt der letzte Beitrag in dem Topic sein.

Einfach raus löschen bringt leider auch nichts. Dann erhalte ich das Ergebnis, dass ich beim Testen schon x-mal hatte, welches aber leider nicht richtig ist.

[gn#36]

So haette ich das hier:

Auf Deutsch:
Ich möchte den Usernamen von demjenigen, der in SEINEM Thema zuletzt geschrieben hat, haben.

allerdings interpretiert.

Welches Ergebnis erhaeltst du denn? Und welches willst du dann genau erhalten? Gib mal ein beispiel mit ein paar kurzen, erfundenen Tabellen. Wenn du das naemlich nicht wolltest frage ich mich was genau dann?

[Seether]

Ok, anders formuliert:
Die Aktualität eines Topics richtet sich ausschließlich nach den Beiträgen des Topic-Starters. Ob danach noch andere gepostet haben ist irrelavant. Entscheidendes Datum ist das Datum des letzten Beitrags des Topic-Startes in seinem Thread.

Mittlerweile denke ich nicht mehr, dass es dafür eine Abfrage gibt. Vermutlich muss man mit Schleifen arbeiten...

[gn#36]

Mal als Beispiel ob ich dich richtig verstehe:

Usertabelle:

Code: Alles auswählen

user_id, username
1, karl
2, heinz

Thementabelle:

Code: Alles auswählen

topic_id, poster_id, topic_title, topic_time
1, 1, Karls Thema 1, 1.1.2000
2, 2, Heinz Thema 1, 2.1.2000

Posts Tabelle

Code: Alles auswählen

post_id, poster_id, topic_id, post_time, post_text
1, 1, 1, 1.1.2000, "Erster Beitrag in Karls erstem Thema"
2, 2, 2, 2.1.2000, "Erster Beitrag in Heinz' erstem Thema"
3, 1, 1, 3.1.2000, "Karls erste Antwort in Karls erstem Thema"
4, 2, 1, 4.1.2000, "Heinz' erste Antwort in Karls erstem Thema"

Wenn ich dich jetzt richtig verstehe moechtest du die Usernamen sortiert nach ihrem letzten Post, wobei aber nur die Posts im eigenen Thema zaehlen. D.h. in diesem Fall waehre die Reihenfolge Karl, Heinz, da Karl zuletzt in seinem eigenen Thema geschrieben hat und Heinz sein Thema schon frueher erstellt hatte. Richtig?

Das muss sich auf jeden Fall per SQL Befehl abfragen lassen, spaetestens mit Subselects muss das machbar sein.

Wie wuerde man das machen, wenn man das ganze einzeln macht?
Wie bekomme ich alle Posts in einem eigenen Thema fuer einen bestimmten User?
Ich brauche alle Beitraege eines Users (WHERE user_id =xy) die in irgend einem Thema sind das der User erstellt hat (JOIN topics ON poster_id = user_id)

Also:

Code: Alles auswählen

SELECT p.* 
FROM posts p
LEFT JOIN topics t ON t.poster_id = p.poster_id
WHERE t.topic_id IS NOT NULL AND p.poster_id =xy

Alternativ ohne JOIN und mit Abfrage aus zwei Tabellen:

Code: Alles auswählen

SELECT p.user_id 
FROM posts p, topics t 
WHERE t.poster_id = p.poster_id
 AND p.poster_id =xy

Jetzt wollen wir das ganze aber sortiert nach Datum des Posts:

Code: Alles auswählen

SELECT p.user_id 
FROM posts p, topics t 
WHERE t.poster_id = p.poster_id
 AND p.poster_id =xy
ORDER BY post_time DESC

Und statt der User ID brauchen wir den Usernamen, also brauchen wir noch eine dritte Tabelle:

Code: Alles auswählen

SELECT u.username 
FROM posts p, topics t, users u
WHERE t.poster_id = p.poster_id
 AND p.poster_id = u.user_id
ORDER BY post_time DESC

Einziges verbleibendes Problem: User tauchen in der Abfrage mehrfach auf, fuer jeden Post in jedem eigenen Thema ein mal. An der Stelle bin ich mir nicht 100% sicher wie man das am besten loesen kann. Die obige Abfrage mittels uebergeordnetem Select neu Sortieren duerfte eine Moeglichkeit sein, aber vermutlich nicht die beste:

Code: Alles auswählen

SELECT DISTINCT username FROM  (SELECT u.username 
FROM posts p, topics t, users u
WHERE t.poster_id = p.poster_id
 AND p.poster_id = u.user_id
ORDER BY post_time DESC)

Eventuell funktioniert GROUP BY username oder auch direkt ein SELECT DISTINCT in der urspruenglichen Abfrage (das wuerde ich zuerst probieren).

[Seether]

Wenn ich dich jetzt richtig verstehe moechtest du die Usernamen sortiert nach ihrem letzten Post, wobei aber nur die Posts im eigenen Thema zaehlen. D.h. in diesem Fall waehre die Reihenfolge Karl, Heinz, da Karl zuletzt in seinem eigenen Thema geschrieben hat und Heinz sein Thema schon frueher erstellt hatte. Richtig?

Exakt


Ich habe mittlerweile ein Lösung, die auch recht schnell ist, allerdings unzählige queries braucht.

Zum Vergleiche normale Forenübersicht lokal: 0.45sec und 10 queries, mit der Abfrage 0.65sec und 100 queries.

Hier ist die Lösung, die kann man sicherlich noch tunen, allerdings bringt mir sowas nur Knoten im Hirn.

Code: Alles auswählen

$sql = "SELECT u.user_id, u.username, t.topic_id
        FROM ".USERS_TABLE." u
        LEFT JOIN ".TOPICS_TABLE." t
        ON u.user_id = t.topic_poster
        WHERE t.forum_id = 1";
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{

 $user_id = $row['user_id'];
 $username = $row['username'];
 $topic_id = $row['topic_id'];
 $sql ="SELECT post_time
        FROM ".POSTS_TABLE."
        WHERE (topic_id = ".$topic_id."
              AND poster_id = ".$user_id.")
        ORDER BY post_time DESC
        LIMIT 1";
 $result2 = $db->sql_query($sql);
 $row2 = $db->sql_fetchrow($result2);
 $db->sql_freeresult($result2);
 $username_array[$row2['post_time']] = $username;



}
$db->sql_freeresult($result);
krsort($username_array);
$username_array = array_slice($username_array,0,10,true);

So, habe nun Deine vorletzte Lösung getestet:
Sie liefert ein Ergebnis, was ich schon x-mal hatte, was aber nicht richtig ist. Außerdem dauert die Abfrage ~1.4sec

Kann man meine Lösung noch was tunen?

[Miriam]

Tunen geht (fast) immer:

Code: Alles auswählen

SELECT u.username, p.poster_id, p.topic_id, p.post_time
FROM phpbb_posts p, phpbb_topics t, phpbb_users u
WHERE p.topic_id = t.topic_id
AND p.poster_id = t.topic_poster
AND u.user_id = p.poster_id
ORDER BY p.post_time DESC
LIMIT 1;

Das zeigt denjenigen an, der als letzter (aus der Gruppe der Topic-Starter) in seinem eigenen Thread gepostet hat. Auch wenn nach ihm/ihr noch weitere User in diesem Thread gepostet haben (deren Thread es aber nicht ist).

[Seether]

Danke für eure Hilfe.

Ich werde es nun per cronjob in eine Datei schreiben und alle 1000 aufrufe oder so aktualisieren lassen. Dann muss halt jemand ne Sekunde warten^^

S.

[Miriam]

Also die Query, die ich vorschlug dauerte in der DB 0,314 Sek bei 30.000 Postings beim ersten Aufruf.
Beim nächsten ist sie ja gecached....