[thelist] MySQL Tables for weblog

[rudy]

> Damn, there I go and forget to ask one of the questions I had.

no prob, lachlan

> Is there some sql way of counting all the comments
> associated with a particular entry,

yes -- see sql below

> or would a seperate field be needed on the entry table
> counting the comments on it? (that seems to violate some
> db design rules...)

needed?  um, no, but perhaps pragmatic

we break this very rule (storing the count of related records) in the evolt
database

using your tables, let's say you want to show the Title, DateAdded, and
Entry from the Entries table, plus the number of Comments records tied to it

there are two approaches --

    select Title, DateAdded, Entry
             , count(*) as CommentCount
        from Entries, Comments
      where Entries.ID = Comments.EntryID
  group by Title, DateAdded, Entry

the GROUP BY is required because of the COUNT() aggregate function -- notice
that the columns being grouped are all from the Entries table

<tip type="rudy's database design rule of thumb #56">
when joining tables in a one-to-many relationship, if all you want is counts
of the related records,  avoid selecting data from the "many" table, so that
the database is free to count records any which way it wants, usually by
counting the number of qualified entries in an index
</tip>

in other words, in your case the database may not have to actually access
the Comments table at all

however, if you do access some field from the joined table,
e.g. the CommentDate ...

    select Title, DateAdded, Entry
             , count(*) as CommentsSinceYouLastVisited
        from Entries, Comments
      where Entries.ID = Comments.EntryID
              and          Comments.DateEntered > [ a date parameter ]
  group by Title, DateAdded, Entry

... then the Comments rows will have to be retrieved (unless there's also an
index on CommentDate, which maybe there oughta be), so the query will run
marginally slower

the second way to do it involves sql99 syntax that perhaps mysql doesn't
support

    select Title, DateAdded, Entry
            , ( select count(*) from Comments
                  where EntryID = Comments.ID ) as CommentsCount
        from Entries

note no GROUP BY, and the subquery is correlated (which means that it
depends on a value from the outer query to run)

now, as to whether you need to store the count...

suppose that you did, as CommentCount

then every time you add a comment...

   insert into Comments ( EntryID, Postername, Email, DateAdded, Comment )
        values ( 21, 'rudy', 'r937 at interlog.com', '2002-03-16',
'whuzzup!!' )

you would also have to update the count

    update Comments
          set CommentCount = CommentCount + 1
        where ID = 21

if you ever bolloxed your database somehow, say by accidentally (or
purposely) removing a comment, or by having the system die after a comment
entry and before the count was updated, you can always go through and
mass-update the counts --

    update Comments
          set CommentCount =
              ( select count(*) from Comments
                  where EntryID = Comments.ID )

except that i'm not sure you can do that with mysql, but you get the idea...

so it's six of one or half dozen of the other (hmmm, a beer would go good
now...)

if the performance of counting rows on the fly is acceptable, i'd leave it
that way


rudy