Thread

This question may be related to a resolved thread.

Hi All

I’m trying to create a list of members, ordered by who’s posted most entries in my News weblog. If they haven’t posted in that weblog, they shouldn’t appear in the list. So far I’ve got to this:

{exp:query sql="SELECT screen_name, total_entries FROM exp_members ORDER BY total_entries DESC"}
 {if "{total_entries}" != "0"}
  {screen_name} [{total_entries}]
 {/if}
{/exp:query}

... which works, but it looks for total_entries in ALL weblogs. Any idea how I can order this by total_entries in just my News weblog?

Thanks, Frank

Hrm- I think this might be a pretty heavy query. 

If I were doing this and really needed the functionality?  I’d write up an add-on that keeps a count per channel (or just for the news channel) for the member.  Either in its own table or possibly just tacked on to your member table a la ‘total_entries’.  You’d still likely need to hash out the group by, because you’ll probably want a ‘sync’ option in the control panel for the extension.  But yea- calculating this on the fly is probably not the best option if you have many entries.

That said, give this a try.  From a quick test, I think it will do what you need.  Just replace the ‘x’ in channel_id = x with the id of the news channel.

SELECT author_id, screen_name, count(*) FROM exp_channel_titles, exp_members WHERE author_id = member_id AND channel_id = x group by author_id

Thanks Robin. I just gave that a go, but got this error (I changed channel to weblog as this site is still on EE1):

MySQL ERROR:
Error Number: 1052
Description: Column ‘weblog_id’ in where clause is ambiguous
Query: SELECT author_id, screen_name, count(*) FROM exp_weblog_titles, exp_members WHERE author_id = member_id AND weblog_id = 8 group by author_id

Is that a dead end? Sorry, I’m a bit lost with queries!