Thread

Hiya,

Hopefully a nice quick easy one this one.

I don’t work with categories a lot but when I do they’re usually very easy to work with, however on a site I need to do my brain has hit a bit of a wall so wondering if someone could push me in the right direction?

Say I have categories like this :

Plastic
——Plastic Subcat 1 (8)
——Plastic Subcat 2 (7)
————Plastic Subcat Subcat 1 (7)
——Plastic Subcat 3 (10)
——Plastic Subcat 4 (5)

Brass
——Brass Subcat 1 (20)
——Brass Subcat 2 (25)
——Brass Subcat 3 (5)

Miscellaneous
——Misce Subcat 1 (2)
——Misce Subcat 2 (1)

So say I now want to show just the main top level of categories on the home page of a site so we show :

Plastic (30)
Brass (50)
Miscellaneous (3)

A user now clicks on one of the main categories shown above and it takes them to a products/category page which then shows any sub-categories for that main category. So far I have all this working with the DynoCat plugin however on this site some of the sub-categories hold products whereas other sub-categories hold more sub-categories and then you get to the products.

How can I find this out dynamically that a sub-category has no other sub-categories in it and so what I’m looking at are products and then be able to take the user to a products/listing page instead?

Hopefully that made some sense. If not then I’ll try to better outline the categories involved which will hopefully help explain this a bit better.

Thanks for any help anyone can give on this.

Best wishes,

Mark

Moved to CodeShare Corner by Moderator

Hey Mark,

Have you seen this plugin? http://devot-ee.com/add-ons/subcategories1/

I haven’t used it myself, but it does seem to have what you want, a {subcategories_count} variable.

Cheers,
Leon

Hmm seems like it would do what I want it to do but unfortunately doing this :

{exp:subcategories}
{subcategories_count}
{/exp:subcategories}

Just outputs subategories_count as literal text to the page instead of a number :-(

Seems I might have to look elsewhere now ;-(

Best wishes,

Mark

Hmm, too bad. Have you tried using a query tag?

Something like:

{exp:query sql="SELECT COUNT(*) AS subcategories_count FROM exp_categories WHERE parent_id='31'"}
Number of subcategories: {subcategories_count}
{/exp:query}

where “31” is the category ID of the category currently being viewed.

Yep that was going to be my next try but unfortunately I’ll need to run that query on every single subcategory each time to see if it has more subcategories which I’m thinking could be a bit of a query overkill for the server?

That said I can’t really see any other way of doing it as I’ll need to look ahead to find out if there are any subcategories so that I can decide what type of page to spit out so I guess this probably really is the only way?

Best wishes,

Mark

I wouldn’t consider it overkill if you would just execute that one query once for a pageview. A plugin would have to do the same to determine if a categories has any subcategories, so I don’t think there is a downside compared to using a plugin.

You could make it a little more efficient by using:

SELECT COUNT(cat_id) AS subcategories_count FROM exp_categories WHERE parent_id='31'

It would be a different story if you’d want to count the number of subcategories for multiple categories on one pageview, then you would be better off using a different query instead of looping through categories and executing the query above multiple times. I hope that makes sense?

I think I might be looking at this all wrong looking at it again.

The problem I have is that I have main top level categories :

Plastic
Brass
Miscellaneous

These are fine to show as they are and will always drill down into sub-categories so no problem there. The problem comes when you drill down a level. Some of the subcategories will show products immediately where as some of the subcategories will have further subcategories before the products show. This means that for every single subcategory link I need to find out before it is clicked on as to whether that is true or not so if it is true then the user gets taken to a products/category page again and if not true then they get taken to the products/listings page which will list the products for that subcategory.

Not sure if that makes sense though?

Best wishes,

Mark

Looking at what I’ve said above and what you’ve said it will be that I will need to loop through many categories at once and execute the query to see if there are any more subcategories before getting to the products listings so not really too sure what other query to run there.

Any ideas?

Thanks loads for all your help on this so far, much appreciated.

Best wishes,

Mark

I seem to have working what I need but it really does add a lot of queries to the page as it has to run for every single category to check if it has sub-categories or not.

{exp:weblog:categories weblog="products"}

{exp:query sql="
SELECT COUNT(*) AS subcategories_count
FROM exp_categories
WHERE parent_id='{category_id}'
"}
{if subcategories_count > "0"}
<a href="http://{path=products/category}">{category_name} - ({subcategories_count})</a>
{if:else}
<a href="http://{path=products/listings}">{category_name}</a>
{/if}


{/exp:query}

{/exp:weblog:categories}

Also I am now not using the DynoCat plugin so I get to see all the categories and sub-categories on the page at once which I really don’t want.

Any ideas? 😉
Pretty please 😊

Best wishes,

Mark

Ah yes, I see what you mean, I’ll try to come up with a new query to get what you want.

Ah yes, I see what you mean, I’ll try to come up with a new query to get what you want.

You Sir are a gent. If you can do that then I’d be massively grateful.

I still have the problem though that this query doesn’t seem to work inside the DynoCat plugin. I’m really not too sure what is going on there though because if you set up the most simplest of uses of the plugin such as :

{exp:dyno_cat:categories weblog="products" show_empty="yes" group_id="2"}

{exp:query sql="
SELECT COUNT(*) AS subcategories_count
FROM exp_categories
WHERE parent_id='{category_id}'
"}
{category_name} - {subcategories_count}
{/exp:query}

{/exp:dyno_cat:categories}

This just spits out the subcategories_count for the category that the page is currently on and not the category_id which DynoCat is currently spitting out.

For instance doing this :

{exp:dyno_cat:categories weblog="products" show_empty="yes" group_id="2"}
{category_id}
{/exp:dyno_cat:categories}

Shows all the correct category IDs but then it appears as though the query doesn’t loop through and use each of these IDs and instead just uses one each and every time.

Grrrrrr!!! It’s getting quite annoying this one as it’s the only thing that is stopping me from getting on with this site :-(

Best wishes,

Mark

Got it working!

{exp:dyno_cat:categories weblog="products" show_empty="yes" group_id="2" parse="inward"}

{exp:query sql="
SELECT COUNT(*) AS subcategories_count
FROM exp_categories
WHERE parent_id='{category_id}'
"}
{category_name} - {subcategories_count}
{/exp:query}

{/exp:dyno_cat:categories}

Added in the parse=“inward” parameter to the dyno_cat plugin which then does the right thing inside the query. It’s probably still running many queries but until I get that different one from yourself 😉 then I’ll probably go with this one just to be able to get on with the site.

Best wishes,

Mark

Good stuff!

I’ve come up with an sql query in the meantime. Could you give this a try to see if it works?

This one will grab all information for all categories and add a subcategories_count to it:

{exp:query sql="SELECT c.*, IFNULL(c2.subcat_count, 0) AS subcategories_count FROM exp_categories c LEFT JOIN (SELECT parent_id, COUNT(*) AS subcat_count FROM exp_categories GROUP BY parent_id) AS c2 ON c.cat_id=c2.parent_id"}
Cat ID: {cat_id}. Cat name: {cat_name}. Subcategories count: {subcategories_count}

{/exp:query}

Now, to narrow that list down, all you need is the category ID of the current category being viewed. For category ID 1 for example, you’ll use this:

{exp:query sql="SELECT c.*, IFNULL(c2.subcat_count, 0) AS subcategories_count FROM exp_categories c LEFT JOIN (SELECT parent_id, COUNT(*) AS subcat_count FROM exp_categories GROUP BY parent_id) AS c2 ON c.cat_id=c2.parent_id WHERE c.parent_id=1"}
Cat ID: {cat_id}. Cat name: {cat_name}. Subcategories count: {subcategories_count}

{/exp:query}

The query executes in 0.0024 sec on my local (slow) server, so nothing to worry about 😊

Cheers,
Leon

To get the category ID of the current category being viewed, I’ve always used this plugin for EE1.x:
http://devot-ee.com/add-ons/category-id/

So, that plugin, mixed with the sql query should get what you want, something like:

{exp:category_id category_group="3" category_url_title="{segment_2}" parse="inward"}
{exp:query sql="SELECT c.*, IFNULL(c2.subcat_count, 0) AS subcategories_count FROM exp_categories c LEFT JOIN (SELECT parent_id, COUNT(*) AS subcat_count FROM exp_categories GROUP BY parent_id) AS c2 ON c.cat_id=c2.parent_id WHERE c.parent_id='{category_id}'"}
Cat ID: {cat_id}. Cat name: {cat_name}. Subcategories count: {subcategories_count}

{/exp:query}
{/exp:category_id}

Let me know how that works out!

Hiya,

The query does work although I get a long long list of all the categories and sub-categories so not too sure how I’d then go about using that in this case?

I do have it working now as in my post above but I’ve just noticed something a bit flaky with the Dyno Cat plugin which has nothing to do with anything being done here so I’m going to have to go and scratch my head again I reckon.

For some reason it shows the category_count for the first top level category but then doesn’t show the category_count for any other top level category but it does show the internal counts. All exceptionally weird and giving me a true headache :-(

Best wishes,

Mark