pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpossible?
2+ messages / 2 participants
[nested] [flat]
* possible?
@ 2003-08-11 19:41 Fejes Jozsef <fejes@tvnetwork.hu>
0 siblings, 1 reply; 2+ messages in thread
From: Fejes Jozsef @ 2003-08-11 19:41 UTC (permalink / raw)
To: pgsql-sql
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 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.
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" contains
- 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 forum.id =
thread.forumid AND thread.id = post.threadid" doesn't return empty threads,
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: possible?
@ 2003-08-11 21:37 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Fejes Jozsef <fejes@tvnetwork.hu>
0 siblings, 0 replies; 2+ messages in thread
From: Franco Bruno Borghesi @ 2003-08-11 21:37 UTC (permalink / raw)
To: Fejes Jozsef <fejes@tvnetwork.hu>; +Cc: pgsql-sql
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=F.id ) AS threadCount,
T.id AS threadId,
( SELECT count(id) FROM posts WHERE threadId=T.id ) AS postCount
FROM
Forum F
INNER JOIN thread T ON (T.forumId=F.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=F.id)
LEFT JOIN (
SELECT T.forumId, count(T.id) AS threadCount FROM thread T GROUP
BY T.forumId
) TC ON (TC.forumId=F.id)
LEFT JOIN (
SELECT P.threadID, count(P.id) AS postCount FROM posts P GROUP
BY P.threadId
) PC ON (PC.threadId=T.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 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.
>
> 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" contains
> - 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 forum.id =
> thread.forumid AND thread.id = post.threadid" doesn't return empty threads,
> 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
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-08-11 21:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-11 19:41 possible? Fejes Jozsef <fejes@tvnetwork.hu>
2003-08-11 21:37 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox