nleft and nright field in categories

[nunoprud]

Hi everyone,

 

I tried insert by mysql query new categories in my shop.

 

I insert sucessfully many rows, but in shop's frontpage in the "created" categories i have the error 404. But if i enter in admin mode in a category and click save, this error disapears. I not made any changes.

 

I compare the category table and after the save, the values of "nleft" and "nright" changed.

 

Anybody can help me how the values are generated?

 

Regards,

[PascalVG]

In /classes/Category.php there are some functions that do the generation of these numbers:

 

public static function regenerateEntireNtree()
{
 $id = Context::getContext()->shop->id;
 $id_shop = $id ? $id: Configuration::get('PS_SHOP_DEFAULT');
 $categories = Db::getInstance()->executeS('
 SELECT c.`id_category`, c.`id_parent`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
  ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
 ORDER BY c.`id_parent`, cs.`position` ASC');
 $categories_array = array();
 foreach ($categories as $category)
  $categories_array[$category['id_parent']]['subcategories'][] = $category['id_category'];
 $n = 1;
 if (isset($categories_array[0]) && $categories_array[0]['subcategories'])
  Category::_subTree($categories_array, $categories_array[0]['subcategories'][0], $n);
}
protected static function _subTree(&$categories, $id_category, &$n)
{
 $left = $n++;
 if (isset($categories[(int)$id_category]['subcategories']))
  foreach ($categories[(int)$id_category]['subcategories'] as $id_subcategory)
   Category::_subTree($categories, (int)$id_subcategory, $n);
 $right = (int)$n++;
 Db::getInstance()->execute('
  UPDATE '._DB_PREFIX_.'category
  SET nleft = '.(int)$left.', nright = '.(int)$right.'
  WHERE id_category = '.(int)$id_category.' LIMIT 1
 ');
}

 

 

These numbers are (for example) used to check for parents of a category in the category tree structure.

For example in classes/Product.php they are used in the function: (See where clause, limiting the result to the category's parent(s)

 

public function getParentCategories($id_lang = null)
{
 if (!$id_lang)
  $id_lang = Context::getContext()->language->id;
 $interval = Category::getInterval($this->id_category_default);
 $sql = new DbQuery();
 $sql->from('category', 'c');
 $sql->leftJoin('category_lang', 'cl', 'c.id_category = cl.id_category AND id_lang = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl'));
 $sql->where('c.nleft <= '.(int)$interval['nleft'].' AND c.nright >= '.(int)$interval['nright']);
 $sql->orderBy('c.nleft');
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
}

 

Maybe draw out your tree and add the left right numbers. This may give you a better understanding.

 

Hope this helps,

pascal

[nunoprud]

Hi pascal,

 

I see the php code and i use my java app that run a php script to update the tree after insert the categories.

 

<?php
include(dirname(__FILE__).'/config/config.inc.php');
Category::regenerateEntireNtree();
?>

 

It works now.

 

Thanks for your quick reply.

 

Regads

[Oulina]

maybe it will help someone

[ricardopxl]

Update 2024 to PS 1.7

The problem is created when you work directly in db, and don't run all the process with the currents controllers. If you have problem with your breadcrumb, but your assoc category (ps_category_product) and id_category_default is correctly (ps_product,ps_product_shop)

The correct and full fix's to update the nleft and nright:

 

Create this file in 

public_html/process/[hidden_folder_name]/regenerate_lf_category.php

 

Excecute only from terminal

php public_html/process/[hidden_folder_name]/regenerate_lf_category.php

<?php 

if (php_sapi_name() !== 'cli') { # Only CLI
    die("Error.");
}

require('../../config/config.inc.php');

ini_set('memory_limit',-1);
set_time_limit(0);

$sql = 'UPDATE '._DB_PREFIX_.'_category SET nleft = 0, nright = 0 WHERE 1';
Db::getInstance()->execute($sql);

$regenerate = Category::regenerateEntireNtree();

echo "INIT: ".date('Y-m-d H:i:s');
echo "\n\n";
print_r($regenerate);
echo "END: ".date('Y-m-d H:i:s');
echo "\n\n";
die("END:13");

Edited March 20 by ricardopxl
More context. (see edit history)

[Homersims]

On 3/20/2024 at 8:55 PM, ricardopxl said:

Update 2024 to PS 1.7

The problem is created when you work directly in db, and don't run all the process with the currents controllers. If you have problem with your breadcrumb, but your assoc category (ps_category_product) and id_category_default is correctly (ps_product,ps_product_shop)

The correct and full fix's to update the nleft and nright:

 

Create this file in 

public_html/process/[hidden_folder_name]/regenerate_lf_category.php

 

Excecute only from terminal

php public_html/process/[hidden_folder_name]/regenerate_lf_category.php

<?php 

if (php_sapi_name() !== 'cli') { # Only CLI
    die("Error.");
}

require('../../config/config.inc.php');

ini_set('memory_limit',-1);
set_time_limit(0);

$sql = 'UPDATE '._DB_PREFIX_.'_category SET nleft = 0, nright = 0 WHERE 1';
Db::getInstance()->execute($sql);

$regenerate = Category::regenerateEntireNtree();

echo "INIT: ".date('Y-m-d H:i:s');
echo "\n\n";
print_r($regenerate);
echo "END: ".date('Y-m-d H:i:s');
echo "\n\n";
die("END:13");

It works now.

 

Thanks for your quick reply.

 

Regads