SQL Query Category Tree

[carbar]

Hi,

 

I need a Sql command to retrieve category list in tree order.

 

Thanks

[vekia]

use:

select * from ps_category c 
INNER JOIN ps_category_lang cl ON c.id_category = cl.id_category

you can define ORDER BY clause and also define language with WHERE clause (where c.id_lang=6)

[carbar]

Thanks, yes the order by clause is a mystery though.

[vekia]

for example:

select * from ps_category c 
INNER JOIN ps_category_lang cl ON c.id_category = cl.id_category
ORDER BY c.id_category ASC

[carbar]

Thanks but that one only sorts by category id which is not necessarily the tree structure.

[vekia]

it was only example ;-)

you can sort it as you want

with other columns

[rash_uks]

have it in a tree

SELECT c.id_parent, c.id_category, CONCAT ( REPLACE(REPLACE(REPLACE(REPLACE(level_depth,1,''),2,'--'),3,'---'),4,'----'),cl.name) as name, cl.description, cl.link_rewrite,cs.position ,level_depth
FROM ps_category c
LEFT JOIN ps_category_lang cl ON (c.id_category = cl.id_category AND id_lang = '1')
LEFT JOIN ps_category_group cg ON (cg.`id_category` = c.id_category)
LEFT JOIN `ps_category_shop` cs ON (c.`id_category` = cs.`id_category` )
WHERE c.id_category <> '1'  
GROUP BY c.id_category
ORDER BY cs.position ASC,c.`id_parent` ASC,level_depth ASC

Edited September 18, 2014 by rash_uks (see edit history)

[sooroos]

how would you get full category path for a product with sql?

[rash_uks]

For product number  8

SELECT CONCAT  ('/',cl.id_category,'-',cl.link_rewrite) as url_widthsef
FROM ps_product AS p
left JOIN ps_category_lang AS cl ON cl.id_category = p.id_category_default
WHERE p.id_product = 8 /* change number 8 for you id product */
GROUP BY cl.id_category

Edited September 18, 2014 by rash_uks (see edit history)

[sooroos]

this is not what i have asked, i meant full category path like: categor --> subcategory --> sub-subcategory....

with your query you select only the last one

[ilisiaraul]

Somebody knows how can i put Categories and subcategories, and product link in this querry? 

SELECT pl.name AS 'Name',
pl.description_short AS 'Short description',
pl.description AS 'Description',
p.price AS 'Price tax excl.',
p.id_product AS 'ID',
p.online_only AS 'Available online only',
pl.link_rewrite AS 'URL rewritten',
p.active AS 'Active (0/1)',
pl.available_later AS 'Text when backorder allowed',
concat( 'http://sensuals.ro/img/p/',mid(im.id_image,1,1),'/', if (length(im.id_image)>1,concat(mid(im.id_image,2,1),'/'),''),if (length(im.id_image)>2,concat(mid(im.id_image,3,1),'/'),''),if (length(im.id_image)>3,concat(mid(im.id_image,4,1),'/'),''),if (length(im.id_image)>4,concat(mid(im.id_image,5,1),'/'),''), im.id_image, '.jpg' ) AS url_image
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_image im ON p.id_product = im.id_product
WHERE 1=1
and p.active = 1

[GT ITECH]

Here is a totally functional function to get categories tree

$ids = array();


function tree($parent=0)
					{
					global $ids;
					if ($parent == 0)
					{
					$results = Db::getInstance()->ExecuteS("SELECT c.id_parent, c.id_category, CONCAT ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(level_depth,1,''),2,''),3,''),4,''),5,''),6,''),cl.name) as name, cl.description, cl.link_rewrite,cs.position ,level_depth
					FROM "._DB_PREFIX_."category c
					LEFT JOIN "._DB_PREFIX_."category_lang cl ON (c.id_category = cl.id_category AND id_lang = '1')
					LEFT JOIN "._DB_PREFIX_."category_group cg ON (cg.`id_category` = c.id_category)
					LEFT JOIN `"._DB_PREFIX_."category_shop` cs ON (c.`id_category` = cs.`id_category` )
					WHERE c.id_category <> '1'  
					GROUP BY c.id_category
					ORDER BY c.`id_parent` ASC,level_depth ASC");
						foreach ($results as $row)
						{
							if (!in_array($row['id_category'], $ids))
							{
							echo '<li id="'.$row['id_category'].'">'.$row['name'].'</br></br>';
							array_push($ids, $row['id_category']);
							tree($row['id_category']);
							}
						}
					
					} else {
						
					$results = Db::getInstance()->ExecuteS("SELECT c.id_parent, c.id_category, CONCAT ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(level_depth,1,''),2,''),3,''),4,''),5,''),6,''),cl.name) as name, cl.description, cl.link_rewrite,cs.position ,level_depth
					FROM "._DB_PREFIX_."category c
					LEFT JOIN "._DB_PREFIX_."category_lang cl ON (c.id_category = cl.id_category AND id_lang = '1')
					LEFT JOIN "._DB_PREFIX_."category_group cg ON (cg.`id_category` = c.id_category)
					LEFT JOIN `"._DB_PREFIX_."category_shop` cs ON (c.`id_category` = cs.`id_category` )
					WHERE c.id_category <> '1'
					AND c.id_parent = '".$parent."'
					GROUP BY c.id_category
					ORDER BY cs.position ASC,c.`id_parent` ASC,level_depth ASC");
						if (!empty($results) > 0)
						{
						echo '<ul>';
						$i = 0;
						$len = count($results);
						foreach ($results as $row)
						{
							if (!in_array($row['id_category'], $ids))
							{
							echo '<li id="'.$row['id_category'].'">'.$row['name'].'</br></br>';
							array_push($ids, $row['id_category']);
							tree($row['id_category']);
							}
							
							if ($i == $len - 1) {
							echo '</ul></li>';
							}
							$i++;
						}	
						
						} else {
							
						echo '</li>';
							
						}
						
					}
					
					}

Then simply use it where you want to show tree :

tree();

You can use jstree to have a good tree presentation compatible with the html code obtained with this function

 

[streletsky]

 

have it in a tree

SELECT c.id_parent, c.id_category, CONCAT ( REPLACE(REPLACE(REPLACE(REPLACE(level_depth,1,''),2,'--'),3,'---'),4,'----'),cl.name) as name, cl.description, cl.link_rewrite,cs.position ,level_depth
FROM ps_category c
LEFT JOIN ps_category_lang cl ON (c.id_category = cl.id_category AND id_lang = '1')
LEFT JOIN ps_category_group cg ON (cg.`id_category` = c.id_category)
LEFT JOIN `ps_category_shop` cs ON (c.`id_category` = cs.`id_category` )
WHERE c.id_category <> '1'  
GROUP BY c.id_category
ORDER BY cs.position ASC,c.`id_parent` ASC,level_depth ASC

why do such complex replacing , which is limited on the one hand. This will be much better : CONCAT( LPAD(  '', c.`level_depth` ,  '-'  ) , cl.`name`  )  AS name