Thread

Friends:

I am using the Member Categories add-on from IntoEEtive.com. I’d like to display members who are associated with a particular category (they have submitted channel entries in that category and I have manually assigned them to that category on that basis in the Member Categories control panel). If that was all I wanted to do, it would be easy.

But the wrinkle is that I want to order the list of members by the count of channel entries they have submitted to that category, in descending order.

The Member Categories add-on does not appear to have that capability.

So I’m thinking the query module might help me here, maybe something like this:

{exp:query limit="10" sql="SELECT exp_members.member_id, exp_members.screen_name, exp_members.bio, exp_members.photo_filename, exp_members.photo_width, exp_members.photo_height, exp_channel_titles.author_id, exp_category_members.member_id, count(exp_channel_titles.author_id) AS total FROM exp_members, exp_channel_titles, exp_category_members WHERE exp_members.member_id = exp_channel_titles.author_id AND exp_channel_titles.author_id = exp.category_members.member_id AND exp_category_members.cat_id = {segment_2_category_id} ORDER BY total DESC"}
/images/member_photos/{photo_filename}<strong>{screen_name}</strong>

{bio} <a href="/index.php/site/authors/{member_id}">See all articles by this author ...</a>

{/exp:query}

(as you can tell, I’m using Low’s Seg2Cat add-on as well - with the category url_title appearing in segment 2).

In the template where I have the above code, the query only returns one member when, in fact, there are 23 members assigned to the category in question in the Member Categories add-on.

I’m not a SQL expert by any stretch of the imagination (which may be obvious to those of you reading this post! <grin>). I’m wondering if anyone can help me out by telling me what I’m doing wrong in the query.

Thanks in advance for any help you can offer - I really appreciate it!

Frank

it looks like you’re very close…

Because I don’t have the same setup as you, I can’t test this query - but it should just be a situation of grouping by the correct field:

try this query (again, untested… but I *think* this should be close)

{exp:query limit="10" sql="
SELECT
 exp_members.member_id,
 exp_members.screen_name,
 exp_members.bio,
 exp_members.photo_filename,
 exp_members.photo_width,
 exp_members.photo_height,
 exp_channel_titles.author_id,
 exp_category_members.member_id,
 count(exp_channel_titles.author_id) AS total
FROM
 exp_members,
 exp_channel_titles,
 exp_category_members
WHERE
 exp_members.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.author_id = exp_category_members.member_id
 AND
 exp_category_members.cat_id = {segment_2_category_id}
GROUP BY
 exp_members.member_id
ORDER BY
 total DESC"}
/images/member_photos/{photo_filename}<strong>{screen_name}</strong>

{bio} <a href="/index.php/site/authors/{member_id}">See all articles by this author ...</a>

{/exp:query}

Thanks Mark!

That is indeed much closer to what I need. It’s now showing all 23 members. But the sort order is not quite right. It’s sorting the members by the number of entries they have submitted in total to all categories rather than by the number of entries they have submitted in the particular category in question.

So I think somehow I have to integrate exp_category_posts and only count the rows in exp_channel_titles for the member’s entries where cat_id matches segment_2_category_id.

Any thoughts on how to make that one last tweak?

Thanks so much for your help - I really appreciate it!

Frank

You’re right on with the exp_category_posts table.  Let me see what we can do.

Quick question:

Do you *need* to assign the person to a category… or can you rely on them having authored an entry which has been assigned to that category?

The query will be simplier if you are not using the member category relationship (but it’s still doable)

regardless of your answer above, one of these two *should* be able to get you rolling.  (again… not tested, this is off the top of my head)

The first one uses a sub select, which is a bit slower, but I think it’s cleaner.  If you’re not dealing with tens of thousands of records, the speed will be just fine.

The second one adds the category posts table as another left join.  This should be faster, but a bit less readable.  Try them both and let me know if they’re broken… I’ll fix this post here so as to not lead someone down a bad road in the future.

.................

Version 1:
(removed to avoid confusion… it didn’t work anyway…)

Version 2:

{exp:query limit="10" sql="
 SELECT
  exp_members.member_id,
  exp_members.screen_name,
  exp_members.bio,
  exp_members.photo_filename,
  exp_members.photo_width,
  exp_members.photo_height,
  exp_channel_titles.author_id,
  exp_category_members.member_id,
  count(exp_category_posts.entry_id) as category_post_count
 FROM
  exp_members,
  exp_category_members,
  exp_channel_titles,
  exp_category_posts
 WHERE
  exp_members.member_id = exp_channel_titles.author_id
  AND
  exp_channel_titles.author_id = exp_category_members.member_id
  AND
  exp_channel_titles.status != 'closed'
  AND
  exp_category_posts.entry_id = exp_channel_titles.entry_id
  AND
  exp_category_members.cat_id = {segment_2_category_id}
 GROUP BY
  exp_members.member_id
 ORDER BY
  category_post_count DESC"}
/images/member_photos/{photo_filename}<strong>{screen_name}</strong>

{bio} <a href="/index.php/site/authors/{member_id}">See all articles by this author ...</a>

{/exp:query}

I think its getting closer.

The sort order of Version 2 doesn’t work.

The sort order of Version 1 seems to work perfectly. But it’s showing multiple instances of each author’s information. You can see what I mean here (that’s part of the draft redesign of the site I’m working on).

The limit (10) isn’t working - but I think that’s another issue.

One final note - in my original post, I introduced an error in how one of the tables was identified. I had this line:

exp_channel_titles.author_id = exp.category_members.member_id

when it should have been this:

exp_channel_titles.author_id = exp_category_members.member_id

(using an underscore instead of a period in the name of the second table referenced on that line). I’m noting this in case anyone ends up copying the code.

Thanks again - I’m getting excited by the prospects, because this is something we’ve been wanting to do for awhile.

Frank

alright then… without the ability to debug the second option, lets just tweak on the first one and see if we can get it right.

1.  I fixed the typo
2.  I remove author_id and the member_id associated with the categories table.  They were redundant

.....

{exp:query limit="10" sql="
SELECT
 exp_members.member_id,
 exp_members.screen_name,
 exp_members.bio,
 exp_members.photo_filename,
 exp_members.photo_width,
 exp_members.photo_height,
 (
 SELECT
  count(*) as howmany
 FROM
  exp_channel_titles as post_count_titles,
  exp_category_posts
 WHERE
  post_count_titles.status != 'closed'
  AND
  post_count_titles.entry_id = exp_category_posts.entry_id
  AND
  post_count_titles.author_id = exp_category_members.member_id
  AND
  exp_category_posts.cat_id = {segment_2_category_id}
 ) as category_post_count
FROM
 exp_members,
 exp_category_members,
 exp_channel_titles
WHERE
 exp_members.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.author_id = exp_category_members.member_id
 AND
 exp_channel_titles.status != 'closed'
 AND
 exp_category_members.cat_id = {segment_2_category_id}
GROUP BY
 exp_members.member_id
ORDER BY
 category_post_count DESC"}
/images/member_photos/{photo_filename}<strong>{screen_name}</strong>

{bio} <a href="/index.php/site/authors/{member_id}">See all articles by this author ...</a>

{/exp:query}

It is now working perfectly! Thank you so much!!!

I was also able to fix the limit problem by adding pagination code.

I am a very happy guy! <grin>

Frank

Hi Mark. Well, I’m back again.

I’m trying to implement a very similar solution on another page - just displaying slightly different information, specifying a particular category by its category id - not referencing the url segment, and adding a limit of 5 within the query (since I won’t be using pagination on this other page).

Here’s the code I am using on that other page:

{exp:query sql="
SELECT
 exp_members.member_id,
 exp_members.screen_name,
 exp_members.photo_filename,
 exp_member_data.m_field_id_4
 (
 SELECT
  count(*) as howmany
 FROM
  exp_channel_titles as post_count_titles,
  exp_category_posts
 WHERE
  post_count_titles.status != 'closed'
  AND
  post_count_titles.entry_id = exp_category_posts.entry_id
  AND
  post_count_titles.author_id = exp_category_members.member_id
  AND
  exp_category_posts.cat_id = 40
 ) as category_post_count
FROM
 exp_members,
 exp_category_members,
 exp_member_data,
 exp_channel_titles
WHERE
 exp_members.member_id = exp_channel_titles.author_id
 AND
 exp_channel_titles.author_id = exp_category_members.member_id
 AND
 exp_channel_titles.status != 'closed'
 AND
 exp_category_members.cat_id = 40
GROUP BY
 exp_members.member_id
ORDER BY
 category_post_count DESC LIMIT 5"}
/images/member_photos/{photo_filename}
<strong>{screen_name}</strong>

{m_field_id_4} <a href="/index.php/risk-transfer/experts/{member_id}">More information ...</a>

{/exp:query}

Unfortunately, I’m getting a SQL error on the page when I use that code. Do you happen to see any obvious errors?

Thanks again,
Frank

put a comma after “exp_member_data.m_field_id_4”

Hi Frank,

It looks like Mark has been able to help you here.

Thanks Mark, nice work. That’s a lot of really awesome community help right there.

Is there anything else you need Frank?

Cheers,

Thanks Mark and Shane. I’m not getting the SQL error any longer, but now I can’t seem to get the contents of m_field_id_4 to display.

Any thoughts?

Thanks,
Frank

Hi Frank,

What is in that field?

If you change the id to something else does it work?

What if you take a way some of the where statements, do you get a match?

Thank you,

Hi Shane. I can answer two of your questions right away:

1. The field contains a short bio of each member - about 10-20 words long. The short name in the Control Panel is shortbio, but in the database, the field name is m_field_id_4.

2. If I change the field to m_field_id_5 (I assume that’s what you meant by your second question), I get the same result - the contents of the field don’t display.

3. If I change the query to this:

{exp:query sql="SELECT exp_member_data.m_field_id_4 FROM exp_member_data"}
{m_field_id_4}
{/exp:query}

the page correctly displays the content of m_field_id_4 for each member.

Before you spend much time on this, though, I have another question. After I tried the code on the different page (where it’s not working), I started thinking about the hit to the server the code might cause. This would be used on the site’s home page and it would be used almost 20 different times - once for each category on the site. I’m starting to wonder if there would be a performance issue and thinking it might be better on the home page for me to display something else.

The thought behind using the code on the home page was to display 5 “topic experts” for each category. But I can probably instead display 1 topic expert - the person who wrote the most recent article in that category, and link to a more extensive listing on topic experts for that category on a separate page. I’m thinking that might be less strain for the system.

Thoughts?

Thanks,
Frank

Hey Frank,

Agreed, I like your proposed idea better than the query. Anything you can do to help with performance is good, and I personally do not like running queries in my templates, or PHP for that matter. If you can do it native, then great! If not, write a module for it and so on.

I am sure Mark may have an opinion about this, he has quite the talent for ExpressionEngine.

Cheers,