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
");
==================================================================
(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: [brownleather] Advanced select query within CMSB framework?
By Jason - July 2, 2012
Hi,
getRecords() will just return every field, so your COUNT and CASE statements aren't directly translatable. What you can do is put create a simple getRecords() call with pagination and a where clause. For the count, you could use the mysql_count(); function inside your foreach loop
example
getRecords() also supports a leftJoin option if needed. You can see an example here:
http://www.interactivetools.com/forum/gforum.cgi?post=72125#72125
getRecords() will just return every field, so your COUNT and CASE statements aren't directly translatable. What you can do is put create a simple getRecords() call with pagination and a where clause. For the count, you could use the mysql_count(); function inside your foreach loop
example
$count_of_children = mysql_count("forum_threads", "hidden = 0 AND type = 'F'");getRecords() also supports a leftJoin option if needed. You can see an example here:
http://www.interactivetools.com/forum/gforum.cgi?post=72125#72125
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
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.
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
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/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/