[thesite] database: content table

[Warden, Matt]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

k,

Rudy's last diagram is located here:
http://members.evolt.org/rudy/oracle4.gif

Now, I noticed some SQL from the weo code that included a replies
field in the content table. The observent will realize that there is
no replies field in the content table. Well, if you're lucky enough
to have access to sql plus:

SQL> desc content;
 Name                                      Null?    Type
 ----------------------------------------- --------
- ----------------------------
 CONTENTID                                 NOT NULL NUMBER(10)
 CONTENTNAME                                        VARCHAR2(50)
 BODY                                               LONG
 USERID                                             NUMBER(10)
 DATEMOD                                            DATE
 SIGNOFF                                            NUMBER(10)
 ADMINID                                            NUMBER(10)
 CATEGORYID                                         NUMBER(10)
 SIGNDATE                                           DATE
 SYNOPSIS                                           VARCHAR2(500)
 RATING                                    NOT NULL NUMBER(3,2)
 RATINGS                                   NOT NULL NUMBER(8)
 REPLIES                                   NOT NULL NUMBER(8)
 KEYPHRASE                                          VARCHAR2(100)

SQL> desc replies;
 Name                                      Null?    Type
 ----------------------------------------- --------
- ----------------------------
 COMMENTID                                          NUMBER(10)
 CONTENTID                                          NUMBER(10)
 USERID                                             VARCHAR2(50)
 MESSAGE                                            LONG
 DATEMOD                                            DATE
 GRRRS                                              NUMBER(5)
 MESSAGENAME                                        VARCHAR2(50)


Now, I'm assuming this was added to avoid a count(replies.commentid)
(and joining the replies table at all) in the "Most Commented On"
box.

However, if I go ahead and make the change I was talking about last
night, I'm going to have to join the replies table anyway to grab the
greatest datemod from replies for that contentid (um, now that I
think about it, could rudy or someone help me out with this query?
once you get into aggregate functions and the like, I tend to not
know how to approach it). To refresh your memory, I'll want something
like this:

select 
    content.contentid, replies, categoryid, contentname,
max(replies.datemod)
from 
    content,
    replies 
where 
    replies > 13
    and contentid <> 3509
    replies.contentid=content.contentid
order by replies.datemod, replies desc

I'm not even sure if that will work, even though there is a
one-to-one relationship between a content record and
max(replies.datemod). *shrug*


thanks,


- --
mattwarden
mattwarden.com

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBOw8RxngH0dUmEhrcEQIhhwCcCK0mjIXOoMfNU6GaozYTk6Jp8OIAoKwc
n7D/MYdJFBrK2Q/cL2P8UFWb
=OGv+
-----END PGP SIGNATURE-----