Message-ID: <001901c2cf3f$3066d4d0$6401a8c0@spook>
From: "John Cavacas" <oogly@rogers.com>
To: <pgsql-sql@postgresql.org>
References: <001501c2cf35$f7584a10$6401a8c0@spook>
	<20030208063539.GA20208@cc.usu.edu>
Subject: Re: query help/sugestions
Date: Sat, 8 Feb 2003 01:56:21 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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@cc.usu.edu>
To: "John Cavacas" <oogly@rogers.com>
Cc: <pgsql-sql@postgresql.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()