[MOD] Displaying products from entire subtree (1.4.8b)

Some shops have lots of categories - bookshops, computer equipment stores and so on. It's common to have an item display that lists all products in all subcategories under the current category, and then filter the results further as the categories become more specific.

Example: selecting the main category "Hard Drives" should list all hard drives regardless of subcategorisation. Selecting the subcategory "SSD Hard Drives" should show all SSD hard drives, and so on.

This isn't supported in the core OpenCart system (you have to navigate to the explicitly assigned subcategory to see the product), but it's not so hard to add some nested set (modified pre-order tree traversal) functionality to make this easy and resource-efficient to do.

NOTE: BACK UP YOUR OPENCART INSTALLATION AND DATABASE BEFORE ATTEMPTING THESE CHANGES!

Step 1: Add two Int(11) fields to the [DB_PREFIX]category table named "lft" and "rgt"

Step 2: In "admin/model/catalog/category.php", add the following recursive function directly below the first line ("class ModelCatalogCategory extends Model {"): Step 3: In the same file, modify the addCategory, editCategory and deleteCategory functions to call the above code. This is done by inserting the following code directly above the last line of the function ("$this->cache->delete('category');"):
Step 4: Go into the admin site of your shop and edit and save a category. This will trigger the tree rebuild for the first time.

Step 5: In the file "catalog/model/catalog/product.php", find the function called getProductsByCategoryId.

You should replace the first line which looks like this:
$sql = "SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "'";

With the following:
Step 6: In the same file, find the function called getTotalProductsByCategoryId. Again, you should replace the first line which looks like this:

$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND p2c.category_id = '" . (int)$category_id . "'"); 

with the following:
VOILA! Now the catalog lists all products under a category whether they belong to the main category itself or one of its subcategories. You can now also modify "catalog\view\theme\default\template\product\category.tpl" to exclude the subcategory listing at the top of the product list page, and just use the left column category navigation itself.

I've had some help from the following resources to put this together:
http://articles.sitepoint.com/print/hie ... a-database
http://dev.mysql.com/tech-resources/art ... -data.html

so how does this affect seo? does this create 1 explicit url to the product?

The mod doesn't affect SEO and doesn't alter the existing product or category data in any way- it just affects what products are shown when a customer selects a category.

Most products will be assigned a category on the deepest level available - for example Hard Drives -> SSD Drives -> Internal. In the standard system, if the customer clicks Hard Drives, he won't see any products unless they have been explicitly assigned to the Hard Drive category (as opposed to any of its subcategories). So usually you end up having to click through to the deepest category before you see any products.

With this mod, if you click Hard Drives, all hard drives are listed. If you click further to SSD Drives, all SSD drives are listed. If you click Internal, all internal SSD hard drives are listed. And so on.

Basically, it's a method of implicitly assigning all the ancestor categories of a subcategory to a given product.

ok i get you, cool, saves me the trouble of having to add a product in a few categories/sub categories.

for example, jewellery store, levels are Marcasite > Rings, Marcasite > Amethyst.

Least with this, i only need drop them into Rings and Amethyst, and it will be listed under marcasite automatically

SXGuy wrote:
for example, jewellery store, levels are Marcasite > Rings, Marcasite > Amethyst.

Least with this, i only need drop them into Rings and Amethyst, and it will be listed under marcasite automatically

Exactly. I might make a module or something out of this later.

I think a module would be a better idea actually because you could have it so people can select which top categories include all sub category items.

rather than have it done to every top level categoy

Yeah - will work on it once I've delivered this project

Do you have any idea how I can use this with version 1.4.9.1? I've already tried to follow the above mentioned steps but it doesn't work.

I haven't had time to look at any data model or controller changes in 1.4.9.1 yet, but the only thing I can imagine failing is the select statements in step 5 and 6.

If you look at them closely, the only change is that instead of selecting on a single category id, I use a subselect to get a set of categories.

So, in 1.4.9.1 try to find the following in the functions getProductsByCategoryId/getTotalProductsByCategoryId (catalog/model/catalog/product.php):
and replace with

Has anyone got this code to work on 1.4.9.4?

Is there a mod like this for 1.5.1.3?

I'd also like something like this for 1.5.1.3. Does anything exist? If not maybe I'll code one up in a while when I have more time.

Anything? I don't want to create a new thread for this.

jordan did you make the mod for 1.5.1.3?