Quote by: cordiste
Text Formatted Code
$stwhere = '';
if( !COM_isAnonUser() )
{
$stwhere .= "({$_TABLES['stories']}.owner_id IS NOT NULL AND {$_TABLES['stories']}.perm_owner IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.group_id IS NOT NULL AND {$_TABLES['stories']}.perm_group IS NOT NULL) OR ";
$stwhere .= "({$_TABLES['stories']}.perm_members IS NOT NULL)";
}
else
{
$stwhere .= "({$_TABLES['stories']}.perm_anon IS NOT NULL)";
}
First, this code is not needed. Your call to COM_getPermSQL does this. And, you should not include anything in your ON clause that is not involved in joining of the tables. Since st.sid = c.sid covers the JOIN, everything else should be in the WHERE clause.
Since you are not doing an outer join, I prefer putting all tables on the FROM statement. (This also eliminates the need for an ON clause.) Also, SQL has table aliases, use them. I eliminated the parentheses as I find them to be clutter.
Text Formatted Code
$sql = "SELECT DISTINCT COUNT(*) AS dups, type, st.title as title, c.comment as comment, c.uid as uid, st.sid as sid, max(c.date) AS lastdate "
. "FROM {$_TABLES['comments']} c, {$_TABLES['stories']} st "
. "WHERE st.sid = c.sid AND st.draft_flag = 0 AND st.commentcode >= 0"
. COM_getPermSQL('AND', 0, 2,'st') // notice we can use st here.
. $topicsql
. COM_getLangSQL('sid', 'AND','st')
. "GROUP BY c.sid,type, c.comment, st.title, st.sid, c.uid "
. "ORDER BY 5 DESC LIMIT 15";
Since you are doing an aggregate, it is a bit ugly to add the username here. Use COM_getDisplayName from lib-common.php. If you look it up, you will see it takes 1 or 5 parameters. So you either pass it uid or you need to join the user table above and select username, remote_user, remote_service, etc. And that just is a mess considering how involved your sql already is.