Post by openconcept » Sat Nov 13, 2010 10:58 pm

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 {"):

Code: Select all

private function rebuildTree($parent, $left) {  
   // the right value of this node is the left value + 1  
   $right = $left+1;  
    // get all children of this node  
   $query = $this->db->query('SELECT category_id FROM '  . DB_PREFIX . 'category WHERE parent_id="'.$parent.'";');  
   foreach ($query->rows as $result) {
       // recursive execution of this function for each  
       // child of this node  
       // $right is the current right value, which is  
       // incremented by the rebuild_tree function  
       $right = $this->rebuildTree($result['category_id'], $right);  
   }  
 
   // we've got the left value, and now that we've processed  
   // the children of this node we also know the right value 
   
   // Since the root category is virtual (not represented in the database), we skip it
   if ($left>1) {
   $this->db->query('UPDATE '  . DB_PREFIX . 'category SET lft='.$left.', rgt='.$right.' WHERE category_id="'.$parent.'";');  
 }
   // return the right value of this node + 1  
   return $right+1;  
}    
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');"):

Code: Select all

$this->rebuildTree(0,1); 
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:

Code: Select all

"$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 IN (SELECT category_id FROM " . DB_PREFIX . "category c1 WHERE c1.lft BETWEEN (SELECT lft from " . DB_PREFIX . "category c2 WHERE c2.category_id='" . (int)$category_id . "') AND (SELECT rgt from " . DB_PREFIX . "category c3 WHERE c3.category_id='" . (int)$category_id . "'))";" 
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:

Code: Select all

$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 IN (SELECT category_id FROM " . DB_PREFIX . "category c1 WHERE c1.lft BETWEEN (SELECT lft from " . DB_PREFIX . "category c2 WHERE c2.category_id='" . (int)$category_id . "') AND (SELECT rgt from " . DB_PREFIX . "category c3 WHERE c3.category_id='" . (int)$category_id . "'))"); 
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
Last edited by openconcept on Thu Dec 09, 2010 7:07 pm, edited 2 times in total.

Daniel A. Øien
Web solutions architect
Open Concept SA, Norway
Web: http://openconcept.no/eng


Newbie

Posts

Joined
Sat Nov 13, 2010 10:34 pm


Post by SXGuy » Sun Nov 14, 2010 5:17 am

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

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by openconcept » Sun Nov 14, 2010 4:09 pm

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.

Daniel A. Øien
Web solutions architect
Open Concept SA, Norway
Web: http://openconcept.no/eng


Newbie

Posts

Joined
Sat Nov 13, 2010 10:34 pm


Post by SXGuy » Sun Nov 14, 2010 4:53 pm

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

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by openconcept » Sun Nov 14, 2010 7:03 pm

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.

Daniel A. Øien
Web solutions architect
Open Concept SA, Norway
Web: http://openconcept.no/eng


Newbie

Posts

Joined
Sat Nov 13, 2010 10:34 pm


Post by SXGuy » Sun Nov 14, 2010 10:58 pm

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

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by openconcept » Mon Nov 15, 2010 3:19 am

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

Daniel A. Øien
Web solutions architect
Open Concept SA, Norway
Web: http://openconcept.no/eng


Newbie

Posts

Joined
Sat Nov 13, 2010 10:34 pm


Post by valentin_harsan » Thu Dec 09, 2010 12:14 am

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.


Posts

Joined
Thu Dec 09, 2010 12:07 am

Post by openconcept » Thu Dec 09, 2010 7:03 pm

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

Code: Select all

p2c.category_id = '" . (int)$category_id . "'"
and replace with

Code: Select all

p2c.category_id IN (SELECT category_id FROM " . DB_PREFIX . "category c1 WHERE c1.lft BETWEEN (SELECT lft from " . DB_PREFIX . "category c2 WHERE c2.category_id='" . (int)$category_id . "') AND (SELECT rgt from " . DB_PREFIX . "category c3 WHERE c3.category_id='" . (int)$category_id . "'))

Daniel A. Øien
Web solutions architect
Open Concept SA, Norway
Web: http://openconcept.no/eng


Newbie

Posts

Joined
Sat Nov 13, 2010 10:34 pm


Post by SupraTwinTurbo » Wed Apr 13, 2011 2:07 pm

Has anyone got this code to work on 1.4.9.4?

User avatar
New member

Posts

Joined
Thu Mar 24, 2011 12:43 pm
Location - NYC, NY USA

Post by mkc » Sat Nov 12, 2011 12:22 am

Is there a mod like this for 1.5.1.3?

mkc
New member

Posts

Joined
Sun Nov 06, 2011 11:20 pm

Post by jordan » Wed Nov 16, 2011 11:09 am

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.

The Veloz Group
Beverly Hills Chairs: The leaders in ergonomic seating
Custom Tobacco: Customized cigars for any occasion


New member

Posts

Joined
Fri Sep 23, 2011 3:55 pm

Post by mkc » Sun Nov 20, 2011 12:12 pm

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

mkc
New member

Posts

Joined
Sun Nov 06, 2011 11:20 pm

Post by kyprios1 » Sun Dec 18, 2011 1:36 pm

jordan did you make the mod for 1.5.1.3?

New member

Posts

Joined
Mon Jan 31, 2011 9:56 am
Who is online

Users browsing this forum: No registered users and 3 guests