Abfrage für eine Forenübersicht

easteregg

easteregg

0
Hallo
ich bin grad dabei für mein Projekt ein kleines forum zu bauen, allerdings habe ich ein paar probleme mit den SQL abfragen.

folgendes db konstrukt:

tabelle categorien: id,name,benutzerlevel
tabelle forum: id,cat_id,name
tabelle topics: id,forum_id,name,user_id,erstellzeit
tabelle posts: id,topic_id,user_id,erstellzeit

die eben über die jeweiligen ids miteinander verknüpft werden sollen.

wenn ich jetzt die forenübersicht erstellen möchte soll das in etwa so aussehen:

Code: 
---------------------------------------------------------
| categoriename                                         |
---------------------------------------------------------
|  | name des forums     | beitragszähler | letzter post|
---------------------------------------------------------
|  | name des forums     | beitragszähler | letzter post|
---------------------------------------------------------

bis dahin kein problem, die abfrage dazu sieht folgendermaßen aus:

Code: 
SELECT
  categorie.name as cat,forum.name,forum.id,topics.name,posts.user_id
FROM
  board_cat as categorie
INNER JOIN
  board_forum as forum
ON
  categorie.id = forum.cat_id
LEFT OUTER JOIN
  board_topics as topics
ON
  topics.forum_id = forum.id
LEFT OUTER JOIN
  board_posts as posts
ON
  topics.id = posts.topic_id

wie kann ich jetzt aber auch absichern, dass ich als user_id und topic.name bei dem left outer join auch immer den letzten beitrag bekomme, und nicht irgend einen?
 
Mit einer WHERE - Klausel, damit kannst du dir ja immer den letzten Datensatz dann aus der Tabelle rausholen.

BTW: mit LIMIT kannste es auch abfragen.

Grüße

Zephyros
 
Ich weiß, SQL kann nervig sein ;) Erst recht wenn die Abfrage so lang werden.

Mit der WHERE - Klausel kannst du ja bekanntlich deine Abfrage auf einen bestimmten String/Integer beschränken.

Bsp.:

Code: 
SELECT user_id FROM user WHERE user_id = 5 oder SELECT user_id FROM user WHERE user_id LIKE "5"

In diesem Falle, wird nur die user_id ausgegeben die die 5 besitzt, im 1. Fall ist es ein INTEGER - Wert und im 2. Fall (mit LIKE) ist es eine Zeichenkette.

Mit der LIMIT - Klausel kannst du deine Abfrage dazu bewegen, ab einem bestimmten Datensatz anzufangen und dir nur eine bestimmte Anzahl an Datensätze dann anzeigen lassen.

Bsp.:

Code: 
SELECT * FROM users LIMIT 0,5

Das heißt nun, es werden nur die Datensätze der Tabelle User ab Datensatz 0 bis zum Datensatz 4 angezeigt, da ja SQL auch bei 0 anfängt zu zählen.

Müsstest mal gucken, wie genau du das am besten bei dir einbauen kannst.

Ach mal wieder ein Edit:
Mit der MIN(ID) oder MAX(ID) kannst du vielleicht einfacher deine Abfrage gestalten, in dem du einfach die MAX - ID des letzten Posts ermittelst.


Grüße

Zephyros
 
wie das limit und where arbeitet ist mir soweit klar, ich haba uch grad festgestellt, dass ich nen allgemeineren denkfehler hab.

oben in dem query bekomm ich ja bei mehreren beiträgen die selbe categorie mehrmals zurück, daher müsste ich ja über die forums.id gruppieren.

also nochmal von vorne, mittlerweile habe ich zwei themen erstellen zum besseren erklären:

Code: 
mysql> SELECT t.name,p.time FROM board_posts AS p INNER JOIN board_topics AS t ON p.topic_id = t.id ORDER BY time DESC;
+-----------+------------+
| name      | time       |
+-----------+------------+
| test2     | 1240328328 |
| testthema | 1240323773 |
+-----------+------------+
2 rows in set (0.00 sec)

ich möchte auf meiner übersichtssseite ja in dem großen query immer nur das letzte thema haben, daher musst ich da ja noch ein group by anhängen!

Code: 
SELECT
  categorie.name as cat,forum.name,forum.id,topics.name,posts.user_id,posts.time
FROM
  board_cat as categorie
INNER JOIN
  board_forum as forum
ON
  categorie.id = forum.cat_id
LEFT OUTER JOIN
  board_topics as topics
ON
  topics.forum_id = forum.id
LEFT OUTER JOIN
  board_posts as posts
ON
  topics.id = posts.topic_id
GROUP BY
  forum.id;

das problem jetzt, wenn ich das so ausführe bekomme ich nicht das test2 als letzten beitrag zurück, sondern testthema, was ja aber so nicht stimmt.

Code: 
mysql> SELECT
    ->   categorie.name as cat,forum.name,forum.id,topics.name,posts.user_id,posts.time
    -> FROM
    ->   board_cat as categorie
    -> INNER JOIN
    ->   board_forum as forum
    -> ON
    ->   categorie.id = forum.cat_id
    -> LEFT OUTER JOIN
    ->   board_topics as topics
    -> ON
    ->   topics.forum_id = forum.id
    -> LEFT OUTER JOIN
    ->   board_posts as posts
    -> ON
    ->   topics.id = posts.topic_id
    -> GROUP BY
    ->   forum.id;
+---------+------+----+-----------+---------+------------+
| cat     | name | id | name      | user_id | time       |
+---------+------+----+-----------+---------+------------+
| testcat | News |  1 | testthema |       3 | 1240323773 |
| testcat | test |  2 | NULL      |    NULL |       NULL |
+---------+------+----+-----------+---------+------------+
2 rows in set (0.00 sec)

sprich ich müsste jetzt ja irgendwie vor dem group by forum.id noch irgendwie sortieren, dass ich den jünsten beitrag bekomme?
mit nem order by oder nem where lässt sich das ja aber nicht bewerkstelligen.
die einzige idee die ich hätte wäre einen subquery einzubauen, aber das ist ja nicht sonderlich performant und irgendwie auch nicht sonderlich elegant.

Code: 
SELECT
  categorie.name as cat,forum.name,forum.id,topics.name,posts.
  (select time FROM board_posts WHERE board_posts.topic_id = topics.id ORDER BY `time` DESC)
FROM
  board_cat as categorie
INNER JOIN
  board_forum as forum
ON
  categorie.id = forum.cat_id
LEFT OUTER JOIN
  board_topics as topics
ON
  topics.forum_id = forum.id
GROUP BY
  forum.id
 
Bei einem JOIN bildest du immer das Kreuzprodukt der beteiligten Datenmengen, das du noch über diverse Kriterien matchst. Das bietet sich immer an, wenn du große Ergebnismengen fetchen willst (z.B. alle Topics eines Posts) oder wenn die Ursprungsdatenmenge sehr klein ist.
Je größer die zusammenmultiplizierte Zielmenge ist, desto unperformanter wird ein JOIN (geschweige denn 4 JOINs).
Da du in deiner Situation (letzter Beitrag) pro Board nur ein Topic selektieren willst, würde ich diese Abfrage separat in einem Query (oder Alternativ in einem Subquery) abfragen. So viel Unterschied macht das nicht, und in dem großen 4-Fach JOIN kommst du sonst nicht vernünftig ran.
Das geschickte Setzen von Inizes (bzw. Fremdschlüsseln) kann an Performance nochmal was rausholen.
mfg, metax.
 
hast du irgendwo dokumentation zu fremdschlüsseln und wie man die sinvoll anwendet?
ich hab von den ganzen indizegeschichten leider gar keine ahnung, da sql für mich ne trail'n'error lernmaßnahme war ;D
 
Nun, ein Index (bzw. Schlüssel) sorgt a priori erstmal dafür, dass über einem Feld eine Art sortiertes Inhaltsverzeichnis (B-Baum oder ähnliches) angelegt wird, so dass du bei einem Direktzugriff auf das Feld nicht alle Datensätze durchsuchen musst, sondern die gesuchten Einträge schnell findest.
Felder, die oft in SELECT-Queries im WHERE- oder HAVING-Teil vorkommen, sind performanter, wenn über ihnen ein Index erstellt wurde.
Ein Primärschlüssel (bzw. in schwächerer Form: Unique) sorgen außerdem noch dafür, dass Werte in einem Feld eindeutig sind. Dadurch kannst du Datensätze immer eindeutig identifieren und die Tabelle hat eine implizite Sortierung. Man sollte in jeder Tabelle einen Primärschlüssel setzen, und sei es nur ein fortlaufendes Nummernfeld (Integer, auto_increment). Ohne Primärschlüssel werden Tabellenzugriffe schnell extrem langsam.
Fremdschlüssel (Foreign Keys) sind Schlüssel, die auf dem Primärschlüssel einer anderen Tabelle referenzieren. Dadurch hast du zusätzlich die Bedingung, dass der Fremdschlüssel nur Werte enthalten darf, die im Primärschlüssel der Referenztabelle vorkommen und du kannst sogar Aktionen definieren, die ausgeführt werden, wenn der Referenzeintrag gelöscht oder modifiziert wird.
z.B.
Tabelle Topic:
----------------
Topic_id, Topic_title, Topic_Icon, ...
Primary Key: Topic_id (auto_increment)


Tabelle Thread
-----------------
Thread_id, Topic_reference, Thread_author, ...
Primary Key: Thread_id
Foreign Key: Topic_reference references Topic (Topic_id)

Ich hoffe, das wird nun klarer.
Fremdschlüssel kannst du in MySQL allerdings nur verwenden, wenn du die neuere DB-Engine "InnoDB" statt "MyISAM" benutzt. Ansonsten nimm für die Fremdschlüssel einfach einen normalen Index.

mfg, metax.
 
Um antworten zu können musst du eingeloggt sein.
Zurück
Oben