Search code examples
sqlexpressionengine

expression engine sql query limit entries


Hi I'm not really too familiar with SQL queries using EE but I think that is what I need here. Any advice would be appreciated. I'm basically trying to create a typical group gallery homepage that links to each members gallery - but I'm not sure how to limit it to just 1 link per author rather than all channel entries.

{exp:channel:entries channel="portfolios" author_id="not 1"}
<a href="{path=portfolios/gallery/{username}}"><img src="{avatar_url}" /></a><br>
<a href="{path=portfolios/gallery/{username}}">{author}</a><br>
{/exp:channel:entries}

Solution

  • This should do the trick. Be sure to replace t.channel_id = 1 with the actual channel_id of your portfolios channel.

    What this will do is list all of the members who have posted entries in that channel. Note that because we're doing an SQL query, {avatar_url} isn't prepared, so we just cheat and hardcode the path to the avatars folder.

    {exp:query sql="
        SELECT m.member_id, m.username, m.screen_name, m.avatar_filename
        FROM exp_members m
        LEFT JOIN exp_channel_titles t
        ON t.author_id = m.member_id
        WHERE t.channel_id = 1
        GROUP BY m.member_id
        ORDER BY m.screen_name ASC
    "}
            <a href="{path=portfolios/gallery/{username}}"><img src="/images/avatars/{avatar_filename}" /></a><br>
            <a href="{path=portfolios/gallery/{username}}">{screen_name}</a><br>
    {/exp:query}