Anzahl der rows, die durch ein GROUP weggelassen werden

Hi! Auf einer Internetseite soll ein kleines Forum eingerichtet werden. Die Beiträge habe ich in einer Tabelle:

id        |refer_to  |titel     |text
____________________________________________
1         |NULL      |Hi!       |Erster Thread!
2         |1         |Re: Hi!   |Eine Antwort
3         |1         |Re: Hi!   |Noch eine Antwort
4         |NULL      |Na ihr    |Ein neuer Thread
5         |4         |Re: Na ihr|Na du.

Der Gedanke: Falls refer_to = NULL, dann ist es ein Thread. Ansonsten ist der Beitrag eine Antwort zum Thread, auf den refer_to zeigt. In diesem Beispiel gibt es also zwei Threads, der erste hat zwei Antworten, der zweite hat eine Antwort.

Auf der Seite möchte ich nun alle Threads auflisten (Durch “SELECT titel, text FROM tabelle WHERE refer_to IS NULL;”), aber auch die Anzahl der abgegebenen Antworten pro Thread. Wie kann ich die sinnvoll in einem Query abfragen? “SELECT refer_to, COUNT(id) FROM tabelle GROUP BY refer_to;” könnte mir sowas ja vielleicht in einem weiteren Query zurückgeben, oder? Schöner wäre aber ein Query.

Als Schmankerl, aber nicht erforderlich, wäre das Datum der letzten Antwort. Ob man das da auch noch unterkriegt? Eine timestamp-column gibt es natürlich.

Danke euch!

Jenny

Das möchtest du so nicht wirklich lösen.

Trenne die Thread-Informationen und die Posting-Informationen in getrennte Tabellen, dann kannst du die Informationen auch sauber auslesen.

Wenn du dein Konzept wirklich so weiterführen möchtest, werden sich deine Anfragen sehr kompliziert gestalten, da du rekursive Anfragen benötigst. Die dafür notwendigen Mittel bringt PostgreSQL erst mit der demnächst erscheinenden Version 8.4.

Also eine Tabelle threads mit id, text, titel und eine Tabelle postings mit id, refer_to, text, titel, timestamp?

Wie kann ich dann die threads mit der Anzahl der dazugehörigen postings auslesen? Und im Idealfall noch den Timestamp der zuletzt geschriebenen Antwort?

Danke! Jenny

Indem du drei verschiedene (oder zumindest zwei verschiedene) Anfragen nutzt.

Die Threadinformationen (Titel, letztes Posting, …) liefern dir genau eine Zeile zurück, die Postings jedoch beliebig viele.

Um mal a bissl zu zeigen, was dann so geht:

test=# select * from forum;
 id | refer_to |    titel    |             inhalt              |   datum
----+----------+-------------+---------------------------------+------------
  1 |          | Hi!         | Erster Thread!                  | 2008-01-02
  2 |        1 | Re: Hi!     | Eine Antwort                    | 2008-01-03
  3 |        1 | Re: Hi!     | noch eine Antwort               | 2008-01-04
  4 |          | na ihr      | ein neuer Thread                | 2008-01-05
  5 |        4 | Re: na ihr  | Na du.                          | 2008-01-06
  6 |        3 | Re: re: Hi! | noch einer mit kapottem Subject | 2008-01-07
(6 rows)

Also etwa das, was Du zeigtest. Dazu noch mit id=6 eine Antwort auf eine Antwort. Nun eine Abfrage, die je Antwort das Level und die Anzahl der Beiträge in diesem level anzeigt. Also, neuer Thread ist Level 1, Antwort ist Level 2, Antwort auf Antwort ist Level 3.

test=# with recursive threads as ( select f.*, 1 as level from forum f where refer_to is null union all select f.*, t.level+1 from forum f  join threads t on (t.id=f.refer_to)) select  id, refer_to, titel, inhalt, level, datum, count(1) over (partition by refer_to) as anzahl_im_level, max(datum) over (partition by refer_to) as letzter_beitrag_im_level from threads order by refer_to nulls first;
 id | refer_to |    titel    |             inhalt              | level |   datum    | anzahl_im_level | letzter_beitrag_im_level
----+----------+-------------+---------------------------------+-------+------------+-----------------+--------------------------
  1 |          | Hi!         | Erster Thread!                  |     1 | 2008-01-02 |               2 | 2008-01-05
  4 |          | na ihr      | ein neuer Thread                |     1 | 2008-01-05 |               2 | 2008-01-05
  2 |        1 | Re: Hi!     | Eine Antwort                    |     2 | 2008-01-03 |               2 | 2008-01-04
  3 |        1 | Re: Hi!     | noch eine Antwort               |     2 | 2008-01-04 |               2 | 2008-01-04
  6 |        3 | Re: re: Hi! | noch einer mit kapottem Subject |     3 | 2008-01-07 |               1 | 2008-01-07
  5 |        4 | Re: na ihr  | Na du.                          |     2 | 2008-01-06 |               1 | 2008-01-06
(6 rows)

Günstig wäre, pro Thread eine separate ID zu führen, das würde einiges vereinfachen. Bin ich abe grad zu faul, und es geht eh erst ab 8.4, und die gibt es noch nicht. Ich verwende die 8.4devel.

Andreas, dem das alles auch noch a bissl fremd ist…