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

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()