Products from subcategories in category view - more than 1 level depth

[valekichia]

Hello!
Does anybody know how to display the products from all levels of sub categories in category view?

I'm using Joomla 3.7.1 and VM 3.2.2

My VM category tree looks like this:

[root]
- main category 1
-- sub category 1
-- sub category 2
-- sub category 3
--- sub sub category 3a
--- sub sub category 3b
--- sub sub category 3c
- main category 2 ...
- main category 3 ...

I set "Show products of subcategories" on yes in VM config.

But what I get is that only products from the first level of subcategory is displayed in top level category.

For example on category 1 page only products from subcategories 1 2 and 3 (= 1st level of depth) are displayed. I'd like also products from sub sub categories 3a 3b and 3c (= 2nd level of depth and all possible sub levels) to be displayed.

Is there a way to achieve this?

Thank you

[marvays]

I'm also interested.

[pinochico]

- You can manually add all subcategories on product
- You can use plugin for automatically add all parent subcategories, if you add only last subcategory on product (attachment)

[balai]

Guys this is a strongly discouraged method to use.
The way it works is by querying each level down your category tree, to find the products.

For example we have that tree:
Parent
-sub1
--sub1.1
--sub1.2
-sub2
--sub2.1
--sub2.2

That means 7 sql queries, when you pick the "parent" category. In a modest scenario this will be extremely counter performant.

+1 for pinochio's answer
To avoid duplicate pages, use the "canonical category" found inside each product.

[pinochico]

to balai:

To avoid duplicate pages, use the "canonical category" found inside each product.

Of course, this info I think is automatically, but maybe is need write there, because what is for me logic and automatycally, for another people maybe not
Thanks

[Rsn]

That means 7 sql queries, when you pick the "parent" category. In a modest scenario this will be extremely counter performant.

We invented an alternative. Just one SQL query for all categories tree!
It works very fast and does not create a large load.
No recursions!
With PHP class - JFTree
Example:

Code Select Expand


// $cat_id = 5;

$tree = new JFTree();
$in = $tree->get_children_list_string($cat_id);
unset($tree);

// query for products - something like this:
$query = '
SELECT p.*
FROM #__virtuemart_product p
LEFT JOIN #__virtuemart_product_categories pc USING(virtuemart_product_id)
WHERE pc.virtuemart_category_id IN ('.$in.')';
$db->setQuery($query);
...


You can find the class JFTree code in the first message of the topic JOOMLAFORUM.RU

[Studio 42]

Rsn,
Your query is short but do not include any exceptions, for eg. Published product.
If you only need products and no caetgory why using your class Jtree
You can directly LEFT join children categories > LEFT join product, to filter the result in 1 query.
Of course if you need next levels, you have to redo same query.
Another solution is to select only child category ID + 1 field with all products using group_concat(productIds) and GROUP BY category child Ids
This mean 1 query only and  you can for eg. get the total of products using count. to optimize....
You have a plenty of solution, so check the best.

[pinochico]

I thought it wouldn't be that simple = a few lines and one function

[Rsn]

Rsn,
Your query is short but do not include any exceptions, for eg. Published product.
If you only need products and no caetgory why using your class Jtree
You can directly LEFT join children categories > LEFT join product, to filter the result in 1 query.
Of course if you need next levels, you have to redo same query.
Another solution is to select only child category ID + 1 field with all products using group_concat(productIds) and GROUP BY category child Ids
This mean 1 query only and  you can for eg. get the total of products using count. to optimize....
You have a plenty of solution, so check the best.

The essence of my proposed solution is to get the necessary data from the category tree as quickly as possible and with minimal load.
Get the id of all subcategories in a particular category.

Add product data - this is simply included in the next request. I showed the next step, like:

Code Select Expand

SELECT p.*
FROM #__virtuemart_product p
...

Isn't there information about the product's publication?

[el02154]

Is there any progress on this? I am also interested in displaying products of all subcategories

[GJC Web Design]

http://forum.virtuemart.net/index.php?topic=138193.msg518424#msg518424