[geeklog-devel] Bug and fix in What's New routine...

[Rob Griffiths]

First, a >HUGE< thanks to Marc for taking the better part of an hour or  
so yesterday to help debug this one!  After my 1.3.7 upgrade went live  
on Sunday afternoon, all looked good.  But a couple of days later, I  
noticed that the What's New box didn't appear to be sorting correctly,  
and comments I knew I had added recently weren't showing up.  I  
compared the box contents with a selection from the COMMENTS table  
sorted by date, and sure enough, many comments were missing.

After much futzing with various queries from Marc as he debugged this  
one, it appears that if you have a few stories with many comments, the  
original SQL will not select a large enough sample to get the 15 most  
recent comments (or something like that; we could get it working by  
expanding the size of the "LIMIT" statement).  Marc re-wrote the query  
in an absolute manner, and all I've done is insert the proper variable  
substitutions.  I haven't even bothered to line break it and make it  
pretty, but it definitely works -- my What's New block now matches the  
comments table sorted by date posted.

In lib-common.php, the original query is:

   $sql = "SELECT DISTINCT count(*) AS  
dups,type,question,{$_TABLES['stories']}.title,{$_TABLES['stories']}.sid 
,qid "
   . "FROM {$_TABLES['comments']} LEFT JOIN {$_TABLES['stories']} ON  
(({$_TABLES['stories']}.sid = {$_TABLES['comments']}.sid) AND (" .  
$stsql . ")) "
   . "LEFT JOIN {$_TABLES['pollquestions']} ON ((qid =  
{$_TABLES['comments']}.sid) AND (" . $posql . ")) WHERE (";
   $sql .= "{$_TABLES['comments']}.date >= (NOW() - INTERVAL  
{$_CONF['newcommentsinterval']} SECOND)) AND ((" .  $stwhere . ") OR ("  
. $powhere . "))";
   $sql .= " GROUP BY {$_TABLES['comments']}.sid ORDER BY  
{$_TABLES['comments']}.date DESC LIMIT 25";

Marc' re-written query is:

   $sql = "SELECT DISTINCT count(*) AS dups, type, question,  
{$_TABLES['stories']}.title, {$_TABLES['stories']}.sid, qid,  
max(comments.date) as lastdate FROM {$_TABLES['comments']} LEFT JOIN  
{$_TABLES['stories']} ON (({$_TABLES['stories']}.sid =  
{$_TABLES['comments']}.sid) AND ({$stsql})) LEFT JOIN  
{$_TABLES['pollquestions']} ON ((qid = {$_TABLES['comments']}.sid) AND  
(({$posql}))) WHERE ({$_TABLES['comments']}.date >= (NOW() - INTERVAL  
{$_CONF['newcommentsinterval']} SECOND)) AND ((({$stwhere})) OR  
(({$powhere}))) GROUP BY {$_TABLES['comments']}.sid ORDER BY 7 DESC  
LIMIT 25";

If someone could clean up the formatting and get this into the next  
minor update, it'd be greatly appreciated.

Thanks again, Marc!

-rob.