I need to display a list of authors that have posted in a specific channel, sorted by number of posts in that channel. I've got this:

{exp:query sql="SELECT screen_name, total_entries, member_id FROM exp_members ORDER BY total_entries DESC"}
        {screen_name} ({total_entries})

... but that counts entries across all channels, not a specific one. Can't seem to find any plugins to help with this, so guessing it's going to be a more complex query.

Thanks loads for any help!


After reading up a bit on mySQL, I've found an answer, which seems to work. If anyone sees that this is horribly inefficient feel free to disagree!

{exp:query sql="SELECT exp_members.screen_name, COUNT(exp_channel_titles.entry_id) AS number_of_posts FROM exp_channel_titles
    LEFT JOIN exp_members
    ON exp_channel_titles.author_id=exp_members.member_id
    LEFT JOIN exp_member_data
    GROUP BY screen_name
    ORDER BY number_of_posts DESC
        {screen_name}, ({number_of_posts})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.