News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

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

Started by valekichia, May 23, 2017, 09:37:50 AM

Previous topic - Next topic

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


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)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

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:

QuoteTo 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 for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

Rsn

Quote from: balai on June 22, 2020, 12:06:07 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:

// $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 :)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

Rsn

Quote from: Studio 42 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.

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:
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

GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation