Recent Topics

1 Dec 21, 2005 18:02    

See subject: Is it possible to show up the 10 or 5 entries with the most comments? That would really be nice. =)

2 Dec 21, 2005 20:32

That's a great idea. It can be done fairly easily with a plugin. I've got one started. I may move this thread to the plugin request forum if you don't mind.

4 Dec 22, 2005 00:18

Way back in the day there was a hack that did this. A plugin for the phoenix generation would be cool.

PS: moved this to plugins request.

5 Dec 22, 2005 00:25

I've got something underway. I have a question, though. Here's the code I'm using:

        $list = new ItemList($blog, array(), '', '', -1, '', array(),
         '', 'DESC', 'comments', 30);

I'm telling it to order by 'comments'. But is that looking at the number of comments for the post, or the comment status of the post (open, closed or disabled)? I was hoping for the former, but it appears to be the latter. If this doesn't work then will I have to build a query (more likely queries) from scratch to get all post ids for the blog, then count the number of comments that match their ids?

EdB, if you can find the old hack I'd like to take a look at it.

6 Dec 22, 2005 01:14

I just changed my sig file but it doesn't seem to be showing up. I wonder why that is? I wonder if characters inside links count against the 255 max, and if so why did it not truncate my new sig file?

http://forums.b2evolution.net/viewtopic.php?t=2682 is one though I think it was very version-specific.

http://forums.b2evolution.net/viewtopic.php?t=4808 links to two others though I didn't follow those links.

http://forums.b2evolution.net/viewtopic.php?t=6189 is probably something you're already hip to eh?

7 Dec 22, 2005 02:21

Those threads are about showing recent comments. I want to show links to the posts that have the most number of comments.

8 Dec 22, 2005 02:28

The details always mess me up!

Most heavily commented would be a unique animal. A good one too! Drive traffic to that which your readers found interesting and you create this global warming type of situation, only a good global warming: not the bad one that ruins the earth and puts snow shovel salesmen out of business.

9 Dec 22, 2005 21:51

I have this almost working. I had to cobble together a big, gnarly SQL query with joins and what not, but it does spit out the post titles and their comment counts. And you can limit it to the top x posts, where x is a number you enter with a plugin param. The default is 5. You can see it here:

http://www.brendoman.com/dev/blogs/index.php

Now I've got to figure out how to generate the permalink url from outside the Item class. There's also a bug for posts that appear on more than one blog. The comments seem to get counted for each blog sometimes.

Here's the meat of the code, for those of you keeping score at home (of for anyone who wants to help):

                $sql = "SELECT DISTINCT post_ID , post_title ,
                COUNT(post_ID ) AS count
                FROM evo_comments , evo_posts
                INNER JOIN evo_postcats ON post_ID = postcat_post_ID
                INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
                WHERE evo_posts.post_ID = evo_comments.comment_post_ID
                and (cat_blog_ID = ".$params['blog'].")
                group by post_ID  ORDER BY `count`  DESC
                LIMIT ".$params['limit'];
                $results = $DB->get_results($sql);
                for ($i = 0; $i < $params['limit']; $i++) {
                        if ($results[$i]->count == '') break;
                        echo $results[$i]->post_title;
                        echo ' ('.$results[$i]->count.')<br />';
                }


If anyone wants, I can give you the full plugin file so you can play with it.

10 Dec 24, 2005 16:51

Got it! It's working now, permalinks and all. Same link as above. I'll clean it up and make a post about it. Then I'll see about a backport to 0.9.1 (as a hack) if you're still using it, MarcDK

13 Dec 25, 2005 21:08

I'm sorry but it does not work correctly for me. The first item has 132 comments. In reality it only has 44. This means he multiplies it with 3.

Any idea?

UPDATE: it also multiplies the third place two times which means that the 35 comments are becoming 70.

UPDATE 2: Ok it seems that every item in this list generated by this script has wrong numbers. The last one is also multiplied with 3 and so is the 4th one. Ony the second one is correct showing 117 comments which is correct. =(

14 Dec 26, 2005 23:16

Hmm, do those posts appear on more than one blog? That's a known bug. Do they appear in more than one category? What about the one that works, is it just in one category?

15 Dec 27, 2005 00:03

Yes, many of my articles appear in more than one category. =/

Can't we just count the categories of an article and divide the number through that?

16 Dec 27, 2005 03:26

Ok, I'm able to replicate this problem on my blog. I added a second category to my post that had the most comments and the comment count doubled in my sidebar. This only affects posts with more than one category in the same blog, not, as I first suspected, posts that show up on multiple blogs (one category for each blog). Here's the query again. I've tried some variations on this, but I can't seem to get rid of this problem. Any help would be great.

SELECT ID, post_title, post_urltitle, COUNT(ID) AS count
FROM evo_comments, evo_posts
INNER JOIN evo_postcats ON ID = postcat_post_ID
INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE evo_posts.ID = evo_comments.comment_post_ID
AND (cat_blog_ID =2)
GROUP BY ID
ORDER BY `count` DESC
LIMIT 5 


This is the query for 0.9.1. The 1.6 version of the plugin uses some different column titles, but has the same problem.

17 Dec 27, 2005 04:48

The bug has been squashed. MarcDK gave me the solution, it just took me a while to see it. I've never used math in a SQL query, but it works like a charm. Line two below is where the magic happens. If you want, you can just change that line, but I'll update the text file linked above with the new code.

SELECT ID , post_title, post_urltitle , 
ROUND(COUNT(ID) / COUNT(DISTINCT postcat_cat_ID)) AS count
FROM evo_comments , evo_posts 
INNER JOIN evo_postcats ON ID = postcat_post_ID 
INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE evo_posts.ID = evo_comments.comment_post_ID
AND (cat_blog_ID =2)
GROUP BY ID
ORDER BY `count` DESC
LIMIT 5

18 Dec 27, 2005 12:53

I'm sorry but the new piece of code give me a blank result:


		$sql = "SELECT ID , post_title, post_urltitle ,
ROUND(COUNT(ID) / COUNT(DISTINCT postcat_cat_ID)) AS count
FROM evo_comments , evo_posts
INNER JOIN evo_postcats ON ID = postcat_post_ID
INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE evo_posts.ID = evo_comments.comment_post_ID
AND (cat_blog_ID =2)
GROUP BY ID
ORDER BY `count` DESC
LIMIT ".$params['limit'];

$sql is empty =/

UPDATE:

Stop, I was wrong. There is something wrong with my template! I'll fix it in a second andf tell you if it works!

UPDATE 2
Ok, I found the error:


AND (cat_blog_ID = ".$params['blog'].")

I changed this line. I think you put the "2" in there because of testing. =) Now it works like a charm! Thank you very much and merry christmas!

19 Dec 27, 2005 15:07

Yeah, like I said, the second line was the only one you needed to drop into your code. Sorry about leaving my test code in there. I'm glad it works for you. Merry Christmas.


Form is loading...