Expression Engine SQL Query help - latest posts by author [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

expression engine sql query entries list by authors

I’m trying to create a short sidebar list of latest posts from a channel, with each post by a separate author. Could someone help as regards merging these queries I think I have (sort of) working separately?

format:

screen name
thumbnail
title

this query code creates a list, but with author_id rather than screen name title works ok, but the thumbnail doesn’t load and the path doesn’t write properly.

{exp:query sql="SELECT t.title, t.author_id FROM exp_channel_titles t 
ORDER BY author_id ASC LIMIT 4"}
{author_id}<br />
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
{/exp:query} 

This query code create the path properly and the user name properly but the title is just appearing as {title} thumbnail doesn’t load.

{exp:query sql="SELECT m.member_id, m.username, m.screen_name, m.avatar_filename FROM exp_members m 
WHERE m.group_id='5' "}
{scr开发者_JAVA技巧een_name}
<a href="{path=portfolios/gallery/{username}}"><img src="{thumbnail}"></a><br>
<a href="{path=portfolios/gallery/{username}}">{title}</a><br />
 {/exp:query} 

I presume I need to use a JOIN but I’m really not that expert with SQL queries.

thumbnail is a field in exp_channel_fields - any idea how I get this into the query? the filename for the image is in exp_channel_data but it is like this -

{filedir_1}13.jpg 

how do I get rid of the

{filedir_1} 

bit or create a path?

or should I be using {exp:channel: in there as well ?

---

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):

{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}