Advanced select query within CMSB framework?

5 posts by 2 authors in: Forums > CMS Builder
Last Post: July 2, 2012   (RSS)

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
");

==================================================================

Re: [Jason] Advanced select query within CMSB framework?

Thanks for the suggestion, two questions.

1 .From an efficiency perspective is using getRecord() much more overhead then how I had it? (say we were taking about 10,000 records)?

2. If its true that there is a significant difference between calling a few fields (the way I had it) vs every field (getRecords) is there a way to specify the desired field while using gerRecords?

Thank you.

Re: [Jason] Advanced select query within CMSB framework?

Also, the way i had it... there was a single call the the database.. using $count_of_children would add say 30 database calls (on per record listing)

Re: [brownleather] Advanced select query within CMSB framework?

By Jason - July 2, 2012

Hi,

You're correct that this does cause some extra overhead, but this doesn't normally cause a problem.

If your query is working the way you like currently, you can manually add in the pagination yourself, but using the LIMIT and OFFSET clauses in mySQL (http://www.petefreitag.com/item/451.cfm).

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/