Top Commenters Page
My old website had a “top commenters” page.
To get the top commenter count I have to thank Ryan Peterson in helping me write this custom MySQL query.I used Group By to lump the results together based on the commenter’s email address. Then use count(*) to count the number of records in the group. Also used the NOT function in MySQL to filter my email address.
SELECT `Comment`.`author`, `Comment`.`id`, `Comment`.`url`, count(*)
AS `count` FROM `cake_comments` AS `Comment` WHERE 1 = 1
AND NOT(`Comment`.`email`=\‘m@marcgrabanski.com\’
Since I didn’t want to load all of the related comments at once, I decied to use a little jQuery and Ajax to show comments that they have made.
First, I put a span tag around the comment count, because without JavaScript you won’t see this functionality. On page load I swapped the spans into links with $(this).replaceWith('<a>' + $(this).html() + '</a>');.
Instead adding behavior later after append, I used a jQuery object inside replaceWith so I can attach behavior to the link and I like how the code looks.
$(this).replaceWith(
$(‘’ + $(this).html() + ‘’).click(function(){ … })
);
Using CakePHP’s JavaScript object generator, $javascript->object($data); it was easy to send JSON back to the client and parse with jQuery. Here is the full source of the JavaScript file.
$(document).ready(function(){
$(‘.get_comments’).each(function(){
$(this).replaceWith(
$(‘<a>’$(this).html()‘</a>’).click(function(){
link = $(this);
$.post(‘comments/get/comments’,
{ ‘data[Comment][id]’: $(this).siblings(‘.author’).attr(‘id’) },
function(data){
out = ‘’;
for (i in data) {
prefix = data[i].Article.type ? ‘article/’ : ‘answers/’;
out += ‘<li><a href="’ + prefix + data[i].Article.slug + ‘#c’ + data[i].Comment.id + ‘">’ + data[i].Article.title + ‘</a>’ + data[i].Comment.created + ‘</li>’;
}
$(‘<ol>’ + out + ‘</ol>’).hide().appendTo(link.parents(‘li:first’)).slideDown();
$(link).replaceWith( $(link).html() );
}, ‘json’);
})
);
});
});
Update: I think I’ll post the CakePHP code just in case someone is interested. Here is the controller, I use the RequestHandler component
var $components = array('RequestHandler');and the Time helpervar $helpers = array('Time');in the top of the controller.
function get($type = null) {
if ($this->RequestHandler->isAjax()) {
Configure::write(‘debug’, 0);
if ($type == ‘comments’) {
$comment = $this->Comment->read(array(‘Comment.email’), $this->data[‘Comment’][‘id’]);
$results = $this->Comment->find(‘all’, array(
‘conditions’ => array(
‘email’ => $comment[‘Comment’][‘email’]),
‘fields’ =>‘Article.title, Article.slug, Article.type, Comment.id, Comment.created’
));
$this->set(compact(‘results’));
}
}
}
I also turn debug off with Configure::write('debug', 0);. Also, I only use $type so that I can setup my code to get types of data if I want later – more of a design pattern I typically follow.
Then in my view I use the time helper and output a JSON object.
<?php if ($result):
if (isset($result[‘Comment’][‘created’])):
$results[$key][‘Comment’][‘created’] = $time->timeAgoInWords($result[‘Comment’][‘created’]);
endif;
echo $javascript->object($results);
?>
To see in action, click the comments count next to someone’s name on the top commenters page.
29 comments
Marc,
Thanks for the props, I am glad I can help out now and again.
~rpeterson
I have a stupid question: why did you use 1=1 in the where clause?
@Ed: ‘WHERE 1=1’ is a technique often used in dynamically-generated SQL queries, where there could be a WHERE clause, but might not be. This way, if there is, you can just say ‘AND foo = bar’. Otherwise you’d have to check to see if a WHERE clause was used, and if so, prepend the additional statement with ’AND ’, otherwise leave it off.
Hope that makes sense. :)
Mike: you might be right. Either..
A. it is left over from using CakePHP query and modifying it. … or
B. Ryan suggested it and I followed along.
Either way, I doubt you need it but this is my personal blog and I’ll leave it in because who cares. =)
Nice job man! And really cool site, it’s very Web 2.0 :)
@Ed: Basically, I put it in there for two reasons:
#1: Marc could see how the SQL was laid out, that is where the Grouped By and things are in relation to WHERE
#2: When there are no conditionals, I find it is nice in code to put a placeholder for future reference, I don’t have to re-read a complex query to find the play I would put a conditional.
Anyways, it’s all personal preference on queries this simple.
Loading the subsections takes more than a second here, thats somewhat too long for not displaying an activity-indicator.
While I’m at it, scrolling your page, at least in Firefox 2 on Windows, is somewhat ugly. The background keeps jumping around – it should be fixed, shouldn’t it?
Update: I posted the CakePHP code on the article too, I’m sure someone out there wants to see it.
Jörn:
#1. I will add an activity indicator sometime soon – there isn’t one because I made this feature in a few hours.
#2. My goal with cross browser on my personal website is to be usable, it doesn’t have to look perfect.
Nice job Mark. Just one comment…
If you use RequestHandler Component, there is no need for the line “$this→layout = ‘ajax’;”, CakePHP will automatically render this view if you use RequestHandler.
Just my two cents.
Sorry, it´s Marc! :)
Martin: Great, thank you. I will take that line off the code in the article.
I think this looks good and I’m suing Firefox!
I don’t know what he’s referring too!
Unless this issue was fixed awhile ago!
This website is very well designed. I’m loving the header. How do you get the CSS hover effect like that? Nevermind I might find the answer here somewhere. Anyway, I will bookmark and digg.
Hey so I just found your blog on accident and I must admit that Ive been reading for the last half hour. Great site.
Has any one tried this out yet? Just seeing if it will help some of my blogs out.
great script although I can’t use it on my website
The issue has been fixed in the new firefox update.
Has any one tried this out yet? Just seeing if it will help some of my blogs out.
awesome article…thank you for share, I will be sure to check out your blog more often
thank you
Ah, nice! Ran across this site on a google search and I’ve been glued to it for almost an hour. Haha. thanks.
This is great info, Thank you.
Excellent post..Keep them coming :)
Thanks for sharing.
I usually don’t post in Blogs but your blog forced me to, amazing work.. beautiful …
This is an excellent post. I was doing research for document sharing and then I came across your blog. You have answered my questions. Thank you vey much.
This is useful information for me, cheers
great post as usual. Maybe will post review about this post in my blog. ! I will follow your tutorials, bookmarked . thanks body
My Twitter account is @1marc you can stay up to date on there.
Followed!
This is a really great post and i love your blog, im joining the call to a video on this site. :)
Wow, did you get help with all that? I want to include a top commentators page or feature on my site so that I can recognise and reward the people who participate the most. I wouldn’t be able to come up with all this by myself.