Get the Top Authors based on entry counts?

Question

I want to be able to display Top Authors based on their entry counts. Has anyone ever tried doing this?

Answer 1

Here's how I did something similar, now edited the code for you to work with users. The better approach would be to do this with a plugin, because, as you can imagine, the only way doing this with Twig will end up in lots of DB queries, so please make sure to use the cache tag with this:

{# Build an associative array of user IDs to the count of related entries #}
{% set userData = [] %}

{# Go through all users #}
{% for user in craft.users %}

    {# Get total number of entries this user is the author of #}
    {% set count = craft.entries.authorId(user.id).total() %}

    {# Save entries count and ALL user data we gonna use to our array #}
    {% set userData = userData|merge([{ entriesCount: count, userId: user.id, userName: user.name }]) %}

{% endfor %}

{# Sort, reverse-sort and pick top 10 elements of the array #}
{% set userData = userData|sort|reverse|slice(0, 10) %}

{# Print the top 10 list #}
{% for element in userData %}
    <li>{{ element.userName }} ({{ element.entriesCount }} entries)</li>
{% endfor %}

Answer 2

A plugin approach might be to create a method in your variables file that queries the database directly:

public function usersByPostCount($limit = '10')
{
    $query = craft()->db->createCommand();
    $users = $query
        ->select('craft_entries.authorId id, COUNT(*) count')
        ->from('entries')
        ->group('craft_entries.authorId')
        ->order('count DESC')
        ->limit($limit)
        ->queryAll();
    return $users;
}

which will return an array:

Array: [{"id":"4","count":"241"},{"id":"24","count":"2"},{"id":"171","count":"2"},{"id":null,"count":"1"}]

that can be accessed using:

<ul>
    {% set usersByPostCount = craft.myPlugin.usersByPostCount(5) %}
    {% for data in usersByPostCount %}
        {% if data.id %}
            {% set user = craft.users.id(data.id).first %}
            {% set postCount = data.count %}
            <li>{{ user.firstName }} {{ user.lastName }} [{{ postCount }}]</li>
        {% endif %}
    {% endfor %}
</ul>

Note: this only returns users that are the author of at least one entry. In order to get all users you would need to join the users table into the query.