Thread

I need some help figuring out how to achieve something with channel entries… I have a channel that I need to group by author.  I have achieved that by orderby=“username”. I’m trying to figure out how I can have that persons name as a heading for all the entries by that user. See Example:

<h1>Username 1</h1>
        -Entry 1
        -Entry 5

<h1>Username 2</h1>
        -Entry 2
        -Entry 3
        -Entry 4

Wouldn’t be too difficult using a little nesting with the exp:query module and the channel entries tag:

(not tested… but should be close, we can refine it to get your desired results)

Assuming the channel ID in question is “1”:

{exp:query sql="SELECT
 username,
 member_id as author_member_id
FROM
 exp_members,
 exp_channel_titles
WHERE
 exp_members.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.channel_id = 1
GROUP BY
 exp_members.member_id"}
<h1>{username}</h1>

<ul>
{exp:channel:entries author_id='{author_member_id}' channel_id=1}
<li>{title}</li>
{/exp:channel:entries}
</ul>

{/exp:query}

again… I haven’t tested this, but it should be close…

Thanks! I think with a little tweaking, this was exactly what I was wanting.

You are correct that my channel_id wasn’t 1, it is actually 59.

I actually need to replace “username” with 2 custom member fields {member_firstname} {member_lastname}. But from what I can tell this could still be achieved with the example you provided.

I modified your code slightly to the following:

{exp:query sql="SELECT
 m_field_id_6, m_field_id_7,
 member_id as author_member_id
FROM
 exp_member_data,
 exp_channel_titles
WHERE
 exp_member_data.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.channel_id = 59
GROUP BY
 exp_member_data.member_id"}
<h1>{m_field_id_6} {m_field_id_7}</h1>
<ul>
{exp:channel:entries channel="referrals" author_id="{author_member_id}"}
<li>{title}</li>
{/exp:channel:entries}
</ul>

{/exp:query}

But I’m only getting one entry title to display under one user. I have 3 authors, and 7 channel entries. I get all three authors to display but only one entry.  Any guidance would be GREATLY appreciated. Thanks.

first, a quick tweak to your SQL will make readability MUCH better for you and those that may look at your code in the future:

... m_field_id_6 as first_name, m_field_id_7 as last_name, ...

then you can use these:

{first_name} {last_name}

...

1. make sure to turn dynamic=‘no’ in your channel entries tag.
http://ellislab.com/expressionengine/user-guide/modules/channel/channel_entries.html#dynamic

2. how about statuses of entries?  are they all “open” or do you have other statuses?
status=“draft|reviewed|published”
http://ellislab.com/expressionengine/user-guide/modules/channel/channel_entries.html#status

those usually get me…

You Sir are a ROCKSTAR! Thank you so much and I appreciate the tweak to my SQL.. I’m always looking for ways to improve as a developer and will definitely use that moving forward.

Hoping you could educate me on own more question. I’m trying to pull one more column from “exp_channel_data” so instead of displaying {title} I could display {referral_member_name} but I don’t get any results. Below is the updated code.

{exp:query sql="SELECT
 m_field_id_6 as first_name, 
 m_field_id_7 as last_name,
 member_id as author_member_id,
 field_id_430 as referral_member_name
FROM
 exp_member_data,
 exp_channel_titles,
 exp_channel_data
WHERE
 exp_member_data.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.channel_id = 59
GROUP BY
 exp_member_data.member_id"}
<h2><strong>{first_name} {last_name}</strong></h2>

<p><ul><br />
{exp:channel:entries channel="referrals" author_id="{author_member_id}" dynamic="no" status="Open|New|Pending|Qualified|Non-Qualified"}<br />
<li>{referral_member_name}</li><br />
{/exp:channel:entries}<br />
</ul></p>

<p>{/exp:query}

Of course - glad to help.

There’s a quirk you’ll experience while working with the exp_channel_data table… that table has an entry_id and a channel_id… but it does NOT carry the author_id - it is only able to be referenced by joining back to the channel_titles table.

BUT - remember that you are grouping by the member_id… so you will only get ONE referral_member_name for each author in this example? (if each member have multiple referrals… then you’ll only see the first one) - is this a desired result?

SELECT
 exp_member_data.m_field_id_6 as first_name, 
 exp_member_data.m_field_id_7 as last_name,
 exp_member_data.member_id as author_member_id,
 exp_channel_data.field_id_430 as referral_member_name
FROM
 exp_member_data,
 exp_channel_titles,
 exp_channel_data
WHERE
 exp_member_data.member_id = exp_channel_titles.author_id
 AND
 exp_channel_data.entry_id = exp_channel_titles.entry_id
 AND
 exp_channel_titles.channel_id = 59
GROUP BY
 exp_member_data.member_id

(untested, but should be close)

No that is not the result I want. Anyway to get all the results?

Thanks for tossing in with this help, mark186282! Looks like you two have a good discussion going here. Since the custom queries goes a bit beyond what I can really help with here, Phil, would you like me to move this thread over to Community Help?

Please do, I didn’t even think to post there… Thanks Kevin.

Hi…

This is pseudo code because I don’t want to debug it perfectly (it’s possibly flawed), but you really want to do this with ONE exp:channel:entries loop for efficiency. So I’d use PHP on output:

$current_header = ''
$display_header = "no"

{exp:channel:entries orderby="username"}
if $current_header != {author}
  $display_header == 'yes"

IF $display_header == 'yes"
  $current_header = {author}
  DISPLAY $current_header
  $display_header = 'no'

other stuff here… like the title and link

{/exp:channel:entries}

So basically, you only display the author header when it switches… and you do it all with one channel:entries loop.

TTFN
Travis

Hey Travis,

Maybe I’m not following completely, but I actually want to show {first_name} {last_name} not {author} so really my original post was modified to the following:

<h1>FirstName LastName</h1>
        -Entry 1
        -Entry 5

<h1>FirstName LastName</h1>
        -Entry 2
        -Entry 3
        -Entry 4

Would I still be able to achieve that with a single loop?

Definitely. As I said, that was just “pseudo code” that shows the approach I’d take. Replace {author} with {first_name} {last_name}.

TTFN
Travis