I have a channel where I'm filtering entries by category and then 'stacking' categories to produce an ever decreasing list of results, i.e. showing all entries with category A and B etc.
In the right hand column I display a list of all categories which the user uses to filter the results, however as I start to stack categories (show all entries with category A and B) the category list on the right starts to more often than not return 'no results' (as expected).
What I would like to achieve is a list of categories on the right which displays only categories which are siblings of those already selected. This is much better for usability because it will guide the user to the categories with results for the stack they are creating.
EDIT:
I guess essentially it's two queries to return a list of 'related' category IDs:
SELECT * FROM exp_category_posts WHERE cat_id IN ({current_category_ids}) GROUP BY entry_id
to get the {entry_ids} related to the current categories ({current_category_ids}) and then:
SELECT cat_id FROM exp_category_posts WHERE entry_id IN ({entry_ids}) GROUP BY cat_id
To get the categories associated with those entries, the question is of course... how does that perform with 1,000's of entries and categories!
Any thoughts? Or am I barking up a very tall tree?
