Ever wanted to show how your site users are contributing? The EE Forums module provides this type of functionality – “View all posts by this member”.
But, what if you want to display a list of your site users and their latest “comments” (???).
To do this you will need to pull in information from several of the database tables. A pretty daunting task for those of us SQL challenged individuals. Hopefully this article will help.
Below is a query that will produce a list of your site members, provide the date they joined your site, provide their avatar (if available), and most importantly their latest comment to the site, it’s date, the weblog entry it was made to, and a link to that entry.
<ul>
{exp:query sql="SELECT
member_id, screen_name AS s_name,
avatar_filename, author_id,
comment, comment_date,
title, t.entry_id,
url_title
FROM exp_members m
LEFT JOIN exp_comments c
ON m.member_id = c.author_id
AND c.comment_date =
( SELECT MAX(comment_date)
FROM exp_comments
WHERE author_id = m.member_id )
LEFT JOIN exp_weblog_titles t
ON c.entry_id = t.entry_id
GROUP BY member_id
ORDER BY join_date, comment_date DESC LIMIT 50"}
<li><h2>{s_name}</h2>
{if avatar_filename ==""}
<a href="path_to_profile"><img src="path_to_placeholder" alt="" /></a><br />
{if:else}
<a href="path_to_profile"><img src="path_to_avatar" /></a><br />
{/if}
{if comment}
<b>Title: <a href="/index.php/template_group/template/{url_title}">{title}</a></b><br />
Last commented: {comment_date format="%D, %F %d, %Y - %g:%i%a"}<br />
{comment}
</li>{if:else}
This users has not contributed any comments</li>{/if}
{/exp:query}
</ul>
Apply some CSS and with a bit of rearranging of the data you get something like:
Category:Templates Category:Queries Category:Comments Category:Members