Listing the members/authors who last posted/edited entries in several channels

Question

My members each have their own "PR-room" where all their entries in several channels + their public profiles are collected and shown to the public.

I want a listings page where the 4 last updated PR-rooms are listed. When I ask for the latest entries/edits with the channel:entries tag on multiple channels, I'll get "duplicates", i.e. if a member has posted in both the "News"- and "Galleries"-channel I'll get two listings for the same member/author.

How can I restrict the list to only one result pr. member, regardless of how many channels they've posted in?

Answer 1

You'll probably want a custom query, where you use the SELECT DISTINCT statement. I'm not certain how you're creating the PR-Rooms, but something like

SELECT DISTINCT m.screen_name, m.member_id FROM exp_members m LEFT JOIN exp_channel_titles c ON m.member_id=c.author_id ORDER BY c.entry_date DESC

Might get you started. Be careful with the LEFT JOIN vs RIGHT JOIN, I didn't test to see which would get you the best results. But basically, a query like this, will get you the member name and their member_id, based on the descending order of the last channel title they created. The DISTINCT limits the results to one return per screen_name and member_id.

Answer 2

EDIT I assumed you wanted to make sure you only had a single entry from each of your channels, but on re-reading your question again I see that's not a requirement. My original answer was therefore overcomplicated, but I'm leaving it here in case it proves of use to someone.

Caveat Lector!

---

So I think the smart way to do this is with Stash and a template partials style approach, as we can grab all the content we need for output and grab the author_id we need in one pass of a {exp:channel:entries} tag per channel, and use a single embed for output.

{!-- we need type="snippet" for parse order stuff, and to parse_tags="yes" so we can grab the rendered output for later--}
{exp:stash:set parse_tags="yes" type="snippet"}
  {exp:channel:entries
    channel="channel_2"
    disable="custom_fields|categories|category_fields|member_data|pagination|trackbacks"
    dynamic="no"
    limit="1"
  }
    {!-- grab the author_id for this entry so we can skip it in next entries tag--}
    {stash:channel_1_author}{author_id}{/stash:channel_1_author}
    {!-- stash the actual content that you want to output later--}
    {stash:content_1}
      <li>{title}</li>
    {/stash:content_1}
  {/exp:channel:entries}
{/exp:stash:set}
{exp:stash:set parse_tags="yes" type="snippet"}
    {!-- now pass our author_id with "not" as a param to the second entrieds tag--}
  {exp:channel:entries
    channel="channel_2"
    dynamic="no"
    disable="custom_fields|categories|category_fields|member_data|pagination|trackbacks"
    limit="1"
    author_id="not {channel_1_author}"
  }
    {stash:channel_2_author}{author_id}{/stash:channel_2_author}
    {stash:content_2}
      <li>{title}</li>
    {/stash:content_2}
  {/exp:channel:entries}
{/exp:stash:set}

{!-- use an embed to handle our output (in which all our fresh tasty snippets will now be available--}
{embed="embed/path"}

In your embed:

<ul>
  {content_1}
  {content_2}
  {content_3}
</ul>

Yes you could probably do all this with a custom query, but you're going to end up with an unreadable mess and (probably) multiple embeds as well due to parse order issues. IMO this way is much cleaner, and makes your intent more readable to the next developer/when you return to your code in 6 months time.

I've been trying to work out if there is a way to do this with only a single channel entries tag for all four entries, but haven't figured it out yet (as we need to make sure we've got 4 entries from unique channels and that each is from a unique author too). Because of all those {exp:channel:entries} tags make sure you use the disable param aggressively and appropriate caching as well.

Alternatively if performance is an issue with this code have a look at the ActiveRecord addon and you may be able to pull what you need with fewer queries using that in place of channel:entries.