Re: query help/sugestions - Mailing list pgsql-sql

On Sat, Feb 08, 2003 at 12:50:20AM -0500, John Cavacas wrote:

Can you please fix your e-mail client so it breaks lines at 72 characters?

> Anyway, i'm trying to solve a problem which I had with my previous version. The problem boils down to how to
efficientlydisplay an article in either in its full form or in a article listing page (think front page of slashdot)
whileat 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),
thenwhile I looped through the results of that query, in PHP I would create another SQL object and issue another query
thatwent out and did a select count(newsid) for each article. This did what I wanted but I always thought it was a bit
ofa hack and it did not perform well under stress.
 

Well, no wonder. For each article you'd issue a new query that had to be
sent to, parsed, planned, optimized, executed, and returned by the
database. All you needed was to join the tables, use a group by or some
other technique.

I suggest you read C.J. Date's "An Introduction to Database Systems".

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

Definitely.

> 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
Ineed, which would be a result set that would look something like this:
 
> 
> newsid | user | date | title | intro | commentcount

You could use a GROUP BY but that could become expensive.

> Another idea I had was to just create a count column in my content table, which gets incremented each time a comment
isposted.
 
> 
> Taking the above idea a step forward, i could problably create a trigger to keep that column counter updated whenver
anew 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.

This has been solved many times by several open source projects (OpenACS
being one of them).

You could, for the page that shoms articles with comment count, do a query
that would join with the comments table, doing a COUNT on it (sorry you
didn't send your table definitions).

For the full article page, you could query for the comment and all the
comments, caching part or all of it in ram if you foresee it'll be
requested often.

-Roberto
-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
if(crash) grab_ankles();kiss_xxx_goodbye()