Thread

Is it possible to sort weblog entries so latest activity will be on top? By latest activity I mean new posts, edits or comments. I’d like to see and order like this:

1) entry that was edited a few seconds ago, but was posted a week ago.
2) entry that was commented on a few minutes ago, but was posted days ago.
3) entry that was posted an hour ago - no other activity.

The idea is that no matter what changed in the post (edit, new post, comment) it would send that post to the top. I know you can put multiple values in the order_by parameter, but that won’t quite fit my needs.

Thanks in advance for any help

I am afraid you’ll need a custom SQL query for that.

I created a custom query that seems to be working well. You end up joining a few tables and using some case logic, so it took a bit of digging around in the database structure. Hopefully this will help someone out in the future. If you need a custom query for weblog entries, or need to sort entries by whichever is newest - comment or entry date. This should work. It could be optimized further, so you weren’t pulling all the comments, but this will work for me right now.

{exp:query sql="SELECT exp_weblog_titles.*, exp_weblog_data.*, exp_members.* 
FROM exp_weblog_titles, exp_weblog_data, exp_members 
WHERE exp_weblog_titles.entry_id = exp_weblog_data.entry_id 
AND exp_weblog_titles.weblog_id = '10' 
AND exp_members.member_id = exp_weblog_titles.author_id 
ORDER BY CASE 
WHEN exp_weblog_titles.entry_date < exp_weblog_titles.recent_comment_date 
AND exp_weblog_titles.recent_comment_date != '0'
        THEN exp_weblog_titles.recent_comment_date
        ELSE exp_weblog_titles.entry_date
        END 
DESC"}
{/exp:query}

Thanks for sharing this query with the rest of us 😊

Finally I found something which really work, I thought all day to find out how to
resolve this problem Stellenmarkt and finally I found the resolving thanks to you:) you help me a
lot and you save my time 😊