Re: query help/sugestions

Roberto,

Thanks for the sugestions. I was actually starting to look down that
path. Here are my table defs:

CREATE TABLE "content" (
"id" int4 DEFAULT nextval('public.content_id_seq') NOT
NULL ,
"userid" int4 NOT NULL ,
"catid" int4 NOT NULL ,
"type" int4 NOT NULL ,
"status" int4 NOT NULL ,
"datecreated" timestamp without time zone DEFAULT now() NOT NULL
,
"lastmodified" timestamp without time zone DEFAULT now() NOT NULL
,
"title" text NOT NULL ,
"intro" text NOT NULL ,
"body" text NOT NULL ,
CONSTRAINT "userid" FOREIGN KEY ("userid") REFERENCES users(id) MATCH
FULL ,
CONSTRAINT "categories_content_fkey" FOREIGN KEY ("catid") REFERENCES
categories(id) MATCH FULL ,
CONSTRAINT "types_content_fkey" FOREIGN KEY ("type") REFERENCES
types(type) MATCH FULL ,
CONSTRAINT "status_content_fkey" FOREIGN KEY ("status") REFERENCES
status(id) MATCH FULL ,
CONSTRAINT "content_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "comments" (
"id" int4 DEFAULT nextval('public.comments_id_seq') NOT
NULL ,
"cid" int4 NOT NULL ,
"pid" int4 NOT NULL ,
"userid" int4 NOT NULL ,
"datecreated" timestamp without time zone DEFAULT now() NOT NULL
,
"lastmodified" timestamp without time zone DEFAULT now() NOT NULL
,
"title" text NOT NULL ,
"commentbody" text NOT NULL ,
"ip" varchar(16) NOT NULL ,
CONSTRAINT "content_comments_fkey" FOREIGN KEY ("cid") REFERENCES
content(id) MATCH FULL ,
CONSTRAINT "comments_pkey" PRIMARY KEY ("id")
);

I set my client to break lines at 72 chars, i hope it worked...

This query:

select c.id, c.title, COUNT(cc.cid) AS ccount FROM content c
LEFT JOIN comments cc ON (c.id=cc.cid) GROUP BY c.id, c.title

Does what I need, however I need to query 3 other tables as well to get
the data that I need to display, trying to make that work now.

Is that what you ment?

Thanks a bunch,
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()