From: John Cavacas <oogly@rogers.com>
To: pgsql-sql@postgresql.org
Subject: query help/sugestions
Date: Sat, 8 Feb 2003 00:50:20 -0500
Message-ID: <001501c2cf35$f7584a10$6401a8c0@spook> (raw)
Hello,
I'm developing a small web application that essentialy lets users post articles 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 PostgreSQL.
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 article.
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 would 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 always 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, which 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 that I think of it, it problably wouldn't work as the trigger could not be generic.
Anyway, I was just wondering if anyone out here had any ideas or past experience that wouldn't mind sharing.
Thanks a bunch,
John
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oogly@rogers.com
Subject: Re: query help/sugestions
In-Reply-To: <001501c2cf35$f7584a10$6401a8c0@spook>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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