I have an advanced query that works as expected.
(lists forum treads with a comment count and the most recent comment date if exists...)
I would like to incorporate this query into the CMSB standard "list... getRecord" method so that I can use CMSB's pagination.
Any ideas?
==================================================================
$result = mysql_query("
SELECT p.num, p.subject, p.user_id, c.hidden, c.type, COUNT(CASE WHEN c.hidden=0 AND c.type='F' then c.num ELSE NULL END) as count_of_children, CASE WHEN COUNT(CASE WHEN c.hidden=0 AND c.type='F' then c.num ELSE NULL END)=0 THEN p.createdDate ELSE MAX(c.createdDate) END as last_date
FROM {$TABLE_PREFIX}forum_threads p
LEFT JOIN {$TABLE_PREFIX}comments c ON p.num=c.parent_id
GROUP BY p.num, p.subject, p.createdDate
ORDER BY last_date DESC
");
==================================================================