expression engine sql query entries list by authors

Question

How would I order the latest 3 entries from a channel with each entry from a different author? (so they don't end up being 3 latest entries by the same author) - I think I need to use an SQL Query for it?

{exp:channel:entries orderby="screen_name|date" channel="portfolios" limit="3" group_id="5" dynamic="no"}
<img src=" {thumbnail}" alt="{title}"/><br />
{title}<br />
{/exp:channel:entries}

Thanks in advance!

score 2 · Accepted Answer · edited May 23 '17 at 11:47

2

Mark - here's a repost of the answer I posted on the other dupe question:

The best approach here, since you need your custom fields parsed, is to first find the entry_ids of the latest 4 entries from distinct authors, and then pass those to the channel:entries tag through an embed using the entry_id parameter.

This should work (be sure to replace the channel_id with the appropriate integer). Replace your entire current chunk of code with this:

{embed="embeds/_latest_per_member" entry_ids="{exp:query sql="SELECT entry_id, author_id FROM exp_channel_titles WHERE entry_date IN( SELECT MAX(entry_date) FROM exp_channel_titles  WHERE status != 'closed' AND channel_id = 1 GROUP BY author_id ) ORDER BY entry_date DESC LIMIT 4" backspace="1"}{entry_id}|{/exp:query}"}

Then your embeds/_latest_per_member template can look something like this:

{exp:channel:entries channel="channel_name" entry_id="{embed:entry_ids}"}
    {author_id}<br />
    <a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
    <a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:channel:entries}

You had mentioned that this code gave you a recursive error - that means that you've put another call to the embed within the embed. Don't do that.

edited May 23 '17 at 11:47

Community

1
1

answered Aug 17 '11 at 05:14

Derek Hogue

4,589
1
15
27

Thanks Derek - I think that works - There is no way I would have figured that out - is status! ee code or sql? does it mean 'not'? what does the backspace=1 do? – mark Aug 17 '11 at 10:34
`!=` means "does not equal" ... it's a non-trivial SQL query overall to be sure, but that's the power of EE - you can do pretty much anything with the right query. For more info on the `backspace` parameter [look here](http://expressionengine.com/user_guide/modules/channel/parameters.html#par_backspace) (many EE module tags use it in exactly the same way). – Derek Hogue Aug 17 '11 at 14:08
what is the backspace removing? seems to work ok if I remove it. is it the | ? does the IN(select MAX find the latest entry date? agree EE is powerful but finding support in the forums really lacking these days. Not a lot of SQL Query examples in there either. – mark Aug 17 '11 at 14:13
Yes, the backspace removes the pipe, but I suppose without it it just adds an extra empty entry_id, which is harmless. The IN([subquery]) is what selects the latest entry grouped by author, which is then ordered chronologically by the main query. – Derek Hogue Aug 17 '11 at 17:56
This query takes over 30 seconds to go over 20K entries – GDmac Nov 17 '17 at 10:50
@GDmac I'd expect as much, sure. – Derek Hogue Nov 21 '17 at 17:59

score 1 · Answer 2 · edited May 23 '17 at 11:57

The WHERE IN (SELECT MAX(entry_date)...) example took a huge hit at the database. It seems to essentially do a subquery for each entry. An alternative i found (Stackoverflow) uses just one subquery in the FROM part,

quote: Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY :-).

Anyway ... This fetches all authors and their last posted entry title. If you also want the url_title, you have to add it to both SELECT statements. I've included some extra options, fetch only for the last 4 months, limited to channel_id 8, and limit to category_id 68.

SELECT author_id, screen_name, title, FROM_UNIXTIME(entry_date) AS m_date 
FROM (
  SELECT t.author_id, t.title, m.screen_name, t.entry_date
  FROM exp_channel_titles AS t
  LEFT JOIN exp_members AS m ON t.author_id = m.member_id
  LEFT JOIN exp_category_posts AS c ON c.entry_id = t.entry_id
  WHERE t.entry_date > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 4 MONTH)) 
  AND t.channel_id = 8
  AND c.cat_id = 68
  ORDER BY t.entry_date DESC
) AS S 
GROUP BY S.author_id
ORDER BY entry_date DESC
# LIMIT 10

expression engine sql query entries list by authors

2 Answers2

Linked

Related