Display List of Authors with Latest Post

Question

I'm trying to display a list of authors in a sidebar along w/ their most recent post next to their name. So something like:

Alan Smith "The Great Beyond"

Brady Crab "In the Midst"

How can I accomplish something like this using Stash?

Answer 1

This solution doesn't use Stash, but you certainly could. This query assumes a broad application, you may be able to simplify it for your needs. The channel_id in the WHERE clauses will need to be modified to match your channel ID, or converted to a WHERE IN () if you are examining more than one channel (or omitted if no channel filtering is needed).

{exp:query sql="SELECT t.author_id, m.screen_name, t.title, t.url_title
        FROM exp_channel_titles AS t
        JOIN (
            SELECT MAX(entry_date) AS max_date, author_id
            FROM exp_channel_titles
            WHERE channel_id = 1
            AND status != 'closed'
            AND entry_date < {current_time}
            GROUP BY author_id
        ) AS t2 ON t.author_id = t2.author_id
        LEFT JOIN exp_members AS m ON m.member_id = t.author_id
        WHERE t.channel_id = 1
        AND status != 'closed'
        AND t.entry_date = t2.max_date
        GROUP BY t.author_id
        ORDER BY screen_name ASC"
    cache="3600"}
    {if count == 1}<ul>{/if}
        <li>{screen_name}: <a href="{path='news/article/{url_title}'}">{title}</a></li>
    {if count == total_results}</ul>{/if}
{/exp:query}

This solution takes future-dated and closed entries into consideration, but additional filtering there may be needed depending on the nature of the content in question.

Answer 2

I won't directly answer the question since Derek has already done so though, I'll add a few links which may be similar to this question and useful to you & or others down the road:

Entries Sorted By Author in ExpressionEngine

Display Recent Authors in Expression Engine (note the comment by GDmac about Stash)