MYSQL: Create a query to display an entry's parent/hirearchy (Breadcumbs)

Question

I have a table like this:

--------------------Categories-------------------
| Category      | Label         | Parent        |
*************************************************
| /             | Home          | NULL          |
-------------------------------------------------
| leasing       | Leasing       | /             |
-------------------------------------------------
| leasing-steps | Leasing Steps | leasing       |
-------------------------------------------------
| new-tenant    | New Tenants   | leasing-steps |
-------------------------------------------------
| properties    | Properties    | leasing       |
-------------------------------------------------
| about         | About Us      | /             |
-------------------------------------------------

I receive a variable (category) from PHP, let's say the value is 'leasing-steps'.

I need to come up with an SQL query that will be able to take any category and give me it's hierarchy, in the proper order. For this example ('leasing-steps'), it would have to output this:

 ---Categories---
| Category      |
*****************
| /             | 
-----------------
| leasing       |
-----------------
| leasing-steps |
-----------------

I tried this:

SELECT `Cat1`.`Category`
FROM Categories AS `Cat1`
LEFT JOIN Categories AS `Cat2` ON `Cat1`.`Parent`
WHERE `Cat1`.`Category` = 'leasing-steps'

But it only gives me 1 entry and not the desired result.

Keep in mind that I am working with an unknown number of parents. it could be 1 level or 99999

see this answer - http://stackoverflow.com/a/11035966/689579 — Sean, Jan 17 '14 at 23:25
This could be accomplished by a php loop to get parent/category and querying it with a new value until you got all but it would be better to modify your database model or by adding a stored procedure/process. — Class, Jan 18 '14 at 02:52

score 2 · Answer 1 · answered Jan 17 '14 at 23:17

2

The problem is your database model, you can fix with a quest if the depth will always be three or less, but this is not a nice solution. Maybe a idea to read the following article on hierarchical data in MySQL: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

answered Jan 17 '14 at 23:17

Pakspul

648
5
17

1

You beat me to posting that link, +1 – Barmar Jan 17 '14 at 23:24
@pakspul Read it and disagree with the solution. I had this problem once, a long time ago and was able to solve it. Unfortunately I do not remember how nor I have access to those archives. The solution is within SQL itself, not by changing the table. – Omar Jan 17 '14 at 23:29
@Omar - unfortunately, you're using MySQL, which is more limited. A number of other RDBMSs have capabilities that support this type of query (often with a recursive CTE). You'll have to 1) change the table, 2) do this in the application layer, 3) define a stored proc, or 4) change RDBMS. – Clockwork-Muse Jan 18 '14 at 02:24

Tin Tran · Answer 2 · 2014-01-18T05:56:31.860

you could write a stored-procedure that creates a temporary table and loop through each node and insert category until the parent is NULL then SELECT from that temporary table ORDER by reverse order so that higher nodes will show up first and then drop that temporary table when you're done like this sqlFiddle

CREATE PROCEDURE get_hierchy(IN in_category varchar(50))
BEGIN
 CREATE TEMPORARY TABLE _categories(id int auto_increment primary key,category varchar(50));
 SET @category = NULL;
 SET @parent = in_category;
 WHILE(@parent IS NOT NULL) DO
    SET @nextparent = @parent;
    SET @parent = NULL;
    SELECT category,parent INTO @category,@parent FROM categories WHERE category = @nextparent;
    INSERT INTO _categories(category) values (@category);
 END WHILE;
 SELECT category FROM _categories ORDER BY id DESC;
 DROP TABLE _categories;
END//

or if you have an auto_incremented id in your categories table and if you know the nodes that are higher up in the hierarchy will always be created first before lower nodes you can use this query (sqlFiddle)

SELECT category
FROM
  (SELECT @parent:=parent as newparent,
          category,parent,
          id
   FROM categories
   JOIN(SELECT @parent:='leasing-steps')init
   WHERE category = @parent
   ORDER BY id DESC
   )cat
ORDER BY id ASC

MYSQL: Create a query to display an entry's parent/hirearchy (Breadcumbs)

2 Answers2