How to list new entries and entries with new comments?

Question

Looking for a way to list new entries, from multiple channels, and existing entries that have received comments - in the last n days. I'd be really grateful for any leads on how to do this!

Best wishes Lee

Answer 1

ExpressionEngine does have an easy way to display recent comments, but it sounds like that's not quite what you're looking for. You need a list of distinct entries having at least one comment in the last N days.

One way to accomplish this is with the Query module:

{exp:query sql=
    "SELECT title
    FROM exp_channel_titles
    JOIN exp_comments
    ON exp_comments.entry_id = exp_channel_titles.entry_id
    WHERE FROM_UNIXTIME(comment_date, '%Y-%m-%d') > DATE_SUB(CURDATE(), INTERVAL 10 DAY)
    GROUP BY exp_channel_titles.entry_id"
}
    <li>{title}</li>
{/exp:query}

This will give a list of entries with comments in the last 10 days.

The key pieces are the last two lines of the query where we compare the comment date against a time interval based on the current date, and then group by entry to prevent duplicates due to multiple qualifying comments on the same entry.