X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A5A29D1E8D2 for ; Mon, 11 Aug 2003 21:34:03 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 44289-04 for ; Mon, 11 Aug 2003 18:32:21 -0300 (ADT) Received: from lucifer.akyasociados.com.ar (unknown [200.69.203.237]) by svr1.postgresql.org (Postfix) with ESMTP id 07AADD1C4D9 for ; Mon, 11 Aug 2003 18:33:39 -0300 (ADT) Received: from taz.oficina (taz.oficina [192.168.1.174]) (authenticated bits=0) by lucifer.akyasociados.com.ar (8.12.9/8.12.9) with ESMTP id h7BLaG4d024801; Mon, 11 Aug 2003 18:36:16 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: possible? From: Franco Bruno Borghesi To: Fejes Jozsef Cc: pgsql-sql@postgresql.org In-Reply-To: <000a01c36040$9c2b9060$a5c8a53e@FAITH> References: <000a01c36040$9c2b9060$a5c8a53e@FAITH> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-NFNs1sUTcRUaPZc1wOdX" Organization: AK y Asociados S.R.L. Message-Id: <1060637857.789.24.camel@taz.oficina> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 11 Aug 2003 18:37:37 -0300 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/107 X-Sequence-Number: 14723 --=-NFNs1sUTcRUaPZc1wOdX Content-Type: multipart/alternative; boundary="=-DRRUMFZQbAmJCtvTAWp/" --=-DRRUMFZQbAmJCtvTAWp/ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable If I don't get it worng, you wanna know how many threads you have for each forum, and how many posts you have for each thread... don't you? maybe something like=20 SELECT F.id AS forumId, ( SELECT count(id) FROM thread WHERE forumId=3DF.id ) AS threadCount, T.id AS threadId, ( SELECT count(id) FROM posts WHERE threadId=3DT.id ) AS postCount FROM Forum F INNER JOIN thread T ON (T.forumId=3DF.id) or perhaps SELECT F.id AS forumId, T.id AS threadId, coalesce(TC.threadCount, 0) AS threadCount, coalesce(PC.postCount, 0) AS postCount FROM forum F INNER JOIN thread T ON (T.forumId=3DF.id) LEFT JOIN ( SELECT T.forumId, count(T.id) AS threadCount FROM thread T GROUP BY T.forumId ) TC ON (TC.forumId=3DF.id) LEFT JOIN ( SELECT P.threadID, count(P.id) AS postCount FROM posts P GROUP BY P.threadId ) PC ON (PC.threadId=3DT.id) should work. On Mon, 2003-08-11 at 16:41, Fejes Jozsef wrote: > Hi! >=20 > I'd like to make a bulletin board, and came upon a strange question: can = the > forum listing be done only with one single SELECT? >=20 > My data structure is like this. Table "forum" holds the main categories, > each with an "id" field. Table "thread" holds the threads, each has it's = own > "id" too, and a "forumid" field that specifies it's parent caregory. Table > "post" holds the posts, with "id", and with a "threadid" field, that > specifies which thread this post belongs to, and also each post has a > timestamp. >=20 > First the user should see a listing of the "forum" table. Then he selects= a > forum, and he should see the a listing of the "thread" table with the > appropriate "forumid". It's not too serious so far. >=20 > Here are my problems: > - order the listings by the time the last post was made > - display how many posts that "forum" or "thread" contains > - for forums, display how many threads are in it >=20 > I checked out some forum implementations so far, and found nothing helpfu= l. > They use too many queries, or don't order by time (like PHPBB). On the ma= in > page, I can list the forums, and then make a query for each of them to te= ll > how many threads and posts are in it, but I just don't like it. By creati= ng > views, I was even able to order by time, but couldn't tell the number of > threads. Also, after creating a new thread, it's empty, so "WHERE forum.i= d =3D > thread.forumid AND thread.id =3D post.threadid" doesn't return empty thre= ads, > so noone can post to it. >=20 > What I'd really love to see is a single SELECT statement for each of the = two > listings that display everything I want. With data views, embedded select= s, > or anything. So, is it possible? >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? >=20 > http://archives.postgresql.org >=20 --=-DRRUMFZQbAmJCtvTAWp/ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable If I don't get it worng, you wanna know how many threads you have for each = forum, and how many posts you have for each thread... don't you?

maybe something like

SELECT
    F.id AS forumId,
    ( SELECT count(id) FROM thread WHERE forumId=3DF.id ) AS= threadCount,
    T.id AS threadId,
    ( SELECT count(id) FROM posts WHERE threadId=3DT.id ) AS= postCount
FROM
    Forum F
    INNER JOIN thread T ON (T.forumId=3DF.id)

or perhaps

SELECT
    F.id AS forumId,
    T.id AS threadId,
    coalesce(TC.threadCount, 0) AS threadCount,
    coalesce(PC.postCount, 0) AS postCount
FROM
    forum F
    INNER JOIN thread T ON (T.forumId=3DF.id)
    LEFT JOIN (
        SELECT T.forumId, count(T.id) AS= threadCount FROM thread T GROUP BY T.forumId
    ) TC ON (TC.forumId=3DF.id)
    LEFT JOIN (
        SELECT P.threadID, count(P.id) A= S postCount FROM posts P GROUP BY P.threadId
    ) PC ON (PC.threadId=3DT.id)

should work.




On Mon, 2003-08-11 at 16:41, Fejes Jozsef wrote: 
Hi!

I'd like to make a bulletin board, and came upon a strange question: can the
forum listing be done only with one single SELECT?

My data structure is like this. Table "forum" holds the main cate=
gories,
each with an "id" field. Table "thread" holds the threa=
ds, each has it's own
"id" too, and a "forumid" field that specifies it's par=
ent caregory. Table
"post" holds the posts, with "id", and with a "thr=
eadid" field, that
specifies which thread this post belongs to, and also each post has a
timestamp.

First the user should see a listing of the "forum" table. Then he=
 selects a
forum, and he should see the a listing of the "thread" table with=
 the
appropriate "forumid". It's not too serious so far.

Here are my problems:
- order the listings by the time the last post was made
- display how many posts that "forum" or "thread" conta=
ins
- for forums, display how many threads are in it

I checked out some forum implementations so far, and found nothing helpful.
They use too many queries, or don't order by time (like PHPBB). On the main
page, I can list the forums, and then make a query for each of them to tell
how many threads and posts are in it, but I just don't like it. By creating
views, I was even able to order by time, but couldn't tell the number of
threads. Also, after creating a new thread, it's empty, so "WHERE foru=
m.id =3D
thread.forumid AND thread.id =3D post.threadid" doesn't return empty t=
hreads,
so noone can post to it.

What I'd really love to see is a single SELECT statement for each of the two
listings that display everything I want. With data views, embedded selects,
or anything. So, is it possible?



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://=
archives.postgresql.org
--=-DRRUMFZQbAmJCtvTAWp/-- --=-NFNs1sUTcRUaPZc1wOdX Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/OAyg21dVnhLsBV0RAoQ0AJ97MjhhbIiSiFXl7npfcbRCH4ZpDgCdG/H2 guhPf1o6gbDC7txzJdIpS48= =RyB0 -----END PGP SIGNATURE----- --=-NFNs1sUTcRUaPZc1wOdX--