Author Topic: Products from subcategories in category view - more than 1 level depth  (Read 1532 times)

valekichia

  • Beginner
  • *
  • Posts: 28
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

  • Jr. Member
  • **
  • Posts: 336
    • richta.cz
  • Skype Name: marvays
I'm also interested.

pinochico

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 716
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
- 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)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1422
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 716
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
to balai:

Quote
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
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

Rsn

  • Jr. Member
  • **
  • Posts: 53
  • Advanced user
    • Blog InformResource
Re: Products from subcategories in category view - more than 1 level depth
« Reply #5 on: October 11, 2020, 19:45:20 pm »
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]
// $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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4378
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Products from subcategories in category view - more than 1 level depth
« Reply #6 on: October 12, 2020, 17:08:56 pm »
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 716
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: Products from subcategories in category view - more than 1 level depth
« Reply #7 on: October 12, 2020, 18:31:33 pm »
I thought it wouldn't be that simple = a few lines and one function :)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

Rsn

  • Jr. Member
  • **
  • Posts: 53
  • Advanced user
    • Blog InformResource
Re: Products from subcategories in category view - more than 1 level depth
« Reply #8 on: October 12, 2020, 19:28:01 pm »
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]
SELECT p.*
FROM #__virtuemart_product p
...

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