To generate a list of ‘most popular’ articles (as judged by the number of comments received) requires a little cunning because comments and articles are stored separately inside Textpattern. There is a native solution already posted, but this can be extended a little further to only show comments within a certain time frame (see below). It’s a tiny bit more efficient as well because it only calls the database once.

To begin, let’s use the catch-all plugin smd_query, as follows:

<txp:smd_query
     query="SELECT ID, Title, Count(parentid) AS num
        FROM txp_discuss AS com
        LEFT JOIN textpattern AS txp
        ON txp.ID = com.parentid
        WHERE com.visible = 1
        GROUP BY com.parentid
        ORDER BY num desc"
     wraptag="ul" break="li">
   <txp:permlink id="{ID}">{Title}</txp:permlink> [ Comments: {num} ]
</txp:smd_query>

Piece by piece

What is going on here? Let’s dissect it a little to find out.

First we get hold of the article’s ID (so we can use it in the <txp:permlink /> tag), its title and the number of comments associated with this article’s ID. We’re calling the comment count num as a shorthand:

SELECT ID, Title, Count(parentid) AS num

Next, is simply some SQL magic to link the txp_discuss table (which holds comments) with the textpattern table (which holds articles). Note again that we’re using the AS operator to let our query know we’re using a shorthand to denote each table’s name. Essentially, this part grabs only those articles that have comments lodged against them:

FROM txp_discuss AS com
LEFT JOIN textpattern AS txp
ON txp.ID = com.parentid

We only want visible comments, not unmoderated or blocked ones:

WHERE com.visible = 1

The clever bit comes next. This simply aggregates the results so that there is only one row returned for each article, with its associated number of comments. Without this you would get the same article repeated, once for each comment that has been made against it:

GROUP BY com.parentid

Finally, sort them in descending order of comment count (num in our case):

ORDER BY num desc

Et voila! The rest of the stuff in the tag’s container just formats a <ul> list with each permlinked article and its number of comments.

Time-based comments

An extension of this technique is to only count articles that have received comments in the last N days, for example comments made in the last week.

For this we need to add just one more line to the query’s WHERE clause:

com.posted > ADDDATE(CURDATE(), INTERVAL -7 DAY)

ADDDATE is a MySQL function that simply takes its first argument (CURDATE() returns the current date) and adds the specified INTERVAL to it. In this case we’re adding -7 days, which does the same as subtracting 7 days. You could use the SUBDATE() function if you preferred to make things clearer.

The INTERVAL can be a variety of things such as INTERVAL -1 MONTH, even HOURs if you wanted.

Incorporating this extra line into our code (and adding a LIMIT so we only get a specific number of results) gives us the top 10 articles by the number of comments received in the past week:

<txp:smd_query
     query="SELECT ID, Title, Count(parentid) AS num
        FROM txp_discuss AS com
        LEFT JOIN textpattern AS txp
        ON txp.ID = com.parentid
        WHERE com.posted > ADDDATE(CURDATE(), INTERVAL -7 DAY)
        AND com.visible = 1
        GROUP BY com.parentid
        ORDER BY num desc
        LIMIT 10"
     wraptag="ul" break="li">
   <txp:permlink id="{ID}">{Title}</txp:permlink> [ Comments: {num} ]
</txp:smd_query>