Re: query help/sugestions

Roberto,

Here's my solution.

CREATE VIEW viewcontent
(id,statusid,title,datecreated,lastmodified,userid,username,catid,catego
ry,intro,body,typeid,content,commentcount) as

SELECT a.id as id,
a.status as statusid,
a.title as title,
a.datecreated as datecreated,
a.lastmodified as lastmodified,
b.id as userid,
b.username as username,
c.id as catid,
c.longname as category,
a.intro as intro,
a.body as body,
d.type as typeid,
d.name as content,
COUNT(e.cid) as commentcount
FROM content a LEFT JOIN comments e ON (a.id = e.cid) , users b,
categories c, types d
WHERE a.id = b.id
AND a.catid = c.id
AND a.type = d.type
AND a.status = 1

GROUP BY

a.id,
a.status,
a.title,
a.datecreated,
a.lastmodified,
b.id,
b.username,
c.id,
c.longname,
a.intro,
a.body,
d.type,
d.name

ORDER BY a.datecreated DESC ;

Is creating the view a good idea in terms of performance? This is a
general query, I would for example not include the main body of the text
needed for the front page listing.

Thanks again,
John

----- Original Message -----
From: "Roberto Mello" <rmello(at)cc(dot)usu(dot)edu>
To: "John Cavacas" <oogly(at)rogers(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: February 8, 2003 1:35 AM
Subject: Re: [SQL] query help/sugestions

> 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 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.
>
> 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 I need, 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 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.
>
> 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()