0

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' "}
{screen_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 ?

Community
  • 1
  • 1
mark
  • 73
  • 2
  • 10

1 Answers1

0

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}
Derek Hogue
  • 4,589
  • 1
  • 15
  • 27
  • thanks derek - but I get the message 'You have caused a template loop due to improperly nested sub-templates ('embeds/latest_per_member' recursively called)'. Here's my basic EE code that works but doesn't show posts 1 per author - it shows posts by all authors if it helps. `

    Latest Entries

    {exp:channel:entries channel="portfolios" orderby="date" ASC limit="3" group_id="5" dynamic="no"} {entry_date format="%j %M %Y"}
    {exp:ce_img:single src="{thumbnail}" height="112" width="150" crop="yes"} 
    {/exp:channel:entries}`

     – mark Aug 16 '11 at 21:50
  • I actually created a new question here [link](http://stackoverflow.com/questions/7081316/expressionengine-latest-entries-by-author) as I thought it might make it simpler to explain – mark Aug 16 '11 at 21:55
  • Mark - continue on [this thread](http://stackoverflow.com/questions/7047022/expression-engine-sql-query-entries-list-by-authors). – Derek Hogue Aug 17 '11 at 05:15