X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 93116474E5C for ; Sat, 8 Feb 2003 00:50:54 -0500 (EST) Received: from fep04-mail.bloor.is.net.cable.rogers.com (fep04-mail.bloor.is.net.cable.rogers.com [66.185.86.74]) by postgresql.org (Postfix) with ESMTP id E8FA1474E4F for ; Sat, 8 Feb 2003 00:50:43 -0500 (EST) Received: from spook ([65.48.10.226]) by fep04-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030208055032.QBDN120822.fep04-mail.bloor.is.net.cable.rogers.com@spook> for ; Sat, 8 Feb 2003 00:50:32 -0500 Message-ID: <001501c2cf35$f7584a10$6401a8c0@spook> From: "John Cavacas" To: Subject: query help/sugestions Date: Sat, 8 Feb 2003 00:50:20 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0012_01C2CF0C.0E422BD0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep04-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sat, 8 Feb 2003 00:50:31 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/166 X-Sequence-Number: 11564 This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C2CF0C.0E422BD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'm developing a small web application that essentialy lets users post arti= cles and then lets other users post comments regarding those articles. This= application is already running actualy, i'm working on a new version. The = current version runs on PHP+MySQL the new version will be using J2EE with P= ostgreSQL. Anyway, i'm trying to solve a problem which I had with my previous version.= The problem boils down to how to efficiently display an article in either = in its full form or in a article listing page (think front page of slashdot= ) while at the same time finding out what the comment count is for that art= icle. I have 2 tables a "content" table which holds the articles, and a "comments= " table that you guessed it holds the comments. In the past what I did was = to create one query that returned all of the articles (remember I was using= MySQL), then while I looped through the results of that query, in PHP I wo= uld create another SQL object and issue another query that went out and did= a select count(newsid) for each article. This did what I wanted but I alwa= ys thought it was a bit of a hack and it did not perform well under stress. Is there a better way to this? I have a few ideas... I was thinking of using a SQL sub select, but to be honest I can't seem to = construct the proper query to give me what I need, which would be a result = set that would look something like this: newsid | user | date | title | intro | commentcount Another idea I had was to just create a count column in my content table, w= hich gets incremented each time a comment is posted. Taking the above idea a step forward, i could problably create a trigger to= keep that column counter updated whenver a new comment is inserted. Now th= at I think of it, it problably wouldn't work as the trigger could not be ge= neric. Anyway, I was just wondering if anyone out here had any ideas or past exper= ience that wouldn't mind sharing. Thanks a bunch, John ------=_NextPart_000_0012_01C2CF0C.0E422BD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello,

I'm developing a small w= eb=20 application that essentialy lets users post articles and then lets other us= ers=20 post comments regarding those articles. This application is already running= =20 actualy, i'm working on a new version. The current version runs on PHP+MySQ= L the=20 new version will be using J2EE with PostgreSQL.

Anyway, i'm trying to so= lve a=20 problem which I had with my previous version. The problem boils down to how= to=20 efficiently display an article in either in its full form or in a article= =20 listing page (think front page of slashdot) while at the same time finding = out=20 what the comment count is for that article.

I have 2 tables a "conte= nt" table=20 which holds the articles, and a "comments" table that you guessed it holds = the=20 comments. In the past what I did was to create one query that returned all = of=20 the articles (remember I was using MySQL), then while I looped through the= =20 results of that query, in PHP I would create another SQL object and is= sue=20 another query that went out and did a select count(newsid) for=20 each article. This did what I wanted but I always thought it was = a bit=20 of a hack and it did not perform well under stress.

Is there a better way to= this? I=20 have a few ideas...

I was thinking of using = a SQL sub=20 select, but to be honest I can't seem to construct the proper query to give= me=20 what I need, which would be a result set that would look something like=20 this:

Another idea I had was t= o just=20 create a count column in my content table, which gets incremented each time= a=20 comment is posted.

Taking the above idea a = step=20 forward, i could problably create a trigger to keep that column counter upd= ated=20 whenver a new comment is inserted. Now that I think of it, it problably wou= ldn't=20 work as the trigger could not be generic.

Anyway, I was just wonde= ring if=20 anyone out here had any ideas or past experience that wouldn't mind=20 sharing.

Thanks a bunch,

John

= ------=_NextPart_000_0012_01C2CF0C.0E422BD0--