A fix for my Mango Popular Posts Pod
December 02 2008 by
Adam
Some people reported problems with my Mango Popular Posts Pod Plugin, and although I haven't verified this yet, I am almost certain I know what the problem is. In my test and production environments, I use SQL Server. In the query that looks up most-commented posts to display, I am using a SELECT TOP statement, which is not valid in all SQL DBMS'. Specifically, it's not valid in MySQL, which is a pretty common DBMS in use on the web. (If you aren't sure if you're using MySQL, and your hosting is pretty cheap, chances are good you are using it…)
I plan on chaning the plugin to work universally, but for now, here's a quick fix you can apply to the code yourself to get it working.
Open up Handler.cfc and change lines 147-152 from this:
<cfset var sqlPopularPosts = "SELECT TOP #variables.intPopularPostCount#
count(#strTablePrefix#comment.id) as CommentCount, #strTablePrefix#entry.id
FROM #strTablePrefix#comment INNER JOIN #strTablePrefix#entry
ON #strTablePrefix#comment.entry_id=#strTablePrefix#entry.id
GROUP BY entry.id
ORDER BY CommentCount DESC"/>
to this:
<cfset var sqlPopularPosts = "SELECT count(#strTablePrefix#comment.id) as CommentCount, #strTablePrefix#entry.id
FROM #strTablePrefix#comment INNER JOIN #strTablePrefix#entry
ON #strTablePrefix#comment.entry_id=#strTablePrefix#entry.id
GROUP BY entry.id
ORDER BY CommentCount DESC
LIMIT 0,#variables.intPopularPostCount#;"/>
That should get it running for you, in MySQL, for now. I will also look into Oracle and PostgreSQL when I implement a universal fix.
Sorry for the delay, I've been working on a pretty neat side-project that's been taking up almost every waking moment (and then some).
Posted in My projects | Mango |
2 comments



Another issue:
GROUP BY entry.id
Should be:
GROUP BY #strTablePrefix#entry.id
Thanks Todd, I'll be sure to include that as well. :)