1

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?

4

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.

  • Thanks for this Derek, works like a charm! One more thing, how can I display certain authors based on a list of author_id? – Roger Erik Tinch May 12 '14 at 20:54
  • You can add an AND WHERE author_id IN (x, y, z) clause, to the subquery in the JOIN, where x, y, z is a comma-delimited list of author_id's . – Derek Jones May 13 '14 at 5:15
0

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)

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.