Thread

Is there a way to list the categories which an author used in a specific channel?

I have a group of authors that post into a single channel which has two category groups assigned to it.  There are no sub-categories in either group.

—-

Channel 1 Categories:

Group A (should not display in result):

Location 1
Location 2
Location 3
...

Group B (shows in result):

Subject 1
Subject 2
Subject 3
...

—-

What I am trying to do is display a member list for a member group that displays which “Group B Category” that the member has entries for while not display the “Group A” categories.

Each entry may have multiple location categories selected, but will always have only one subject category selected.

—-

Here is what I am trying to end up with:

[avatar] Member 1 - Email
Has posted in: Subject 1, Subject 3

[avatar] Member 2 - Email
Has posted in: Subject 3

[avatar] Member 3 - Email
Has posted in: Subject 2, Subject 3

[avatar] Member 4 - Email
Has posted in: Subject 1, Subject 2, Subject 3
...

—-

I know this will require the Query Module, but I am not familiar enough with it to get anything close to working.  I am not sure how to join the users’ posts to the categories and then display the categories only once in the result.

I did not find anything close to this functionality after digging around in the forums for about hour or so.

Any help would be greatly appreciated.

Thanks,

Steve

I don’t know if I understand what u want exactly but I think this is what u want:

http://rog.ee/category_sorted_entries

or the free version:

http://rog.ee/category_sorted_entries_lite

I hope, this query would help you:

SELECT exp_members.username AS member_name, exp_categories.cat_name AS category_name, 
exp_channel_titles.entry_id, exp_channel_titles.title
FROM exp_categories 
JOIN exp_category_posts ON exp_category_posts.cat_id = exp_categories.cat_id
JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_category_posts.entry_id
JOIN exp_members ON exp_channel_titles.author_id = exp_members.member_id
WHERE exp_members.member_id =  '[member_id]'
AND exp_channel_titles.status='[open|closed]'

Thank you Bhashkar!

That was just what I needed to work it out.  I haven’t used the query module before, but now that I have, I can see almost endless possibilities.

Here was what I ended up with:

{exp:user:users group_id="9" orderby="member-last-name" sort="asc"}<-- SOLSPACE USER MODULE

...Other Stuff…

{exp:query sql="

SELECT
  exp_members.username,
  exp_members.screen_name,
  exp_members.member_id,
  exp_categories.cat_name AS category_name,
  exp_categories.group_id,
  exp_channel_titles.entry_id,
  exp_channel_titles.title,
  exp_categories.cat_id
FROM
  exp_categories
JOIN exp_category_posts
  ON exp_category_posts.cat_id = exp_categories.cat_id
JOIN exp_channel_titles
  ON exp_channel_titles.entry_id = exp_category_posts.entry_id
JOIN exp_members 
  ON exp_channel_titles.author_id = exp_members.member_id
WHERE 
  exp_categories.group_id = 2
AND 
  member_id = '{member_id}'

" backspace="7"}

  {category_name}, 

{/exp:query}

{/exp:user:users}

The Only question I have now is how would I limit the query the only result for each unique match in WHERE exp_categories.group_id = 2.

For example, if a user has many entries across several categories in group_id 2, where they might have used a category more than once, how would I limit the query to returning each used category in group 2 only one time, even though there might be several entries with the same category?

Does that make sense?

Thanks again.

Hi Launch,

Sorry, I didn’t get your points properly.

As this query is returning the all the entries + other stuffs which having any category of category group 2. So would you like to fetch only categories (of category group 2) which ever been assigned to any channel entry.
If so you can use GROUP BY in the same query after the WHERE clause.

Like GROUP BY exp_categories.cat_id

Let me know if you were talking about the same.

Best Regards,
Bhashkar

Thanks again, that did it.