How to sort category by path and sort order?

Question

first of all I am sory for my english.

My category structure is shown below. My purpose is to get category structure with single query. I sort my table by path and I get category tree.

sort_order : Indicates sort order between categories with same level

When I run query

SELECT * FROM category order by path;

RESULT:

But result must be as shown below because sort_order of Electronic is 1 and this is less than sort_order value of Computer category 2

 - ELectronic 
 - - TV 
 - - - LCD 
 - - - - LED LCD 
 - Computer 
 - - Laptop

EDIT :

For @adopilot

Query : SELECT * FROM category order by level, path;

RESULT:

Answer 1

The problem is you need to extract the sort order for the root items and make that same order apply to the sub-items in the tree without re-ordering the sub-items. In Oracle you can do this with a windowing function as follows:

SELECT RPAD('- ',Length(Path)-1,'- ') || label, levelx, path, sort_order
   , min(sort_order) OVER (PARTITION BY FirstLevel) Sort_Order2
FROM (
   SELECT id, parent_id, label, levelx, path, sort_order
      , substr(path,2,1) FirstLevel 
   FROM t1 WHERE Label IS NOT NULL
)
ORDER BY Sort_Order2, Path;

I don't know if you can do something like that in MYSQL, so here is a version using a GROUP BY and self join that works in MySQL 5.5.28.

SELECT Label
FROM T1 a
JOIN
   (SELECT SUBSTR(Path,2,1) FirstLevel, MIN(Sort_Order) FirstSort FROM T1 
      WHERE Label IS NOT NULL GROUP BY SUBSTR(Path,2,1)) b
   ON substr(a.Path,2,1) = b.FirstLevel
ORDER BY FirstSort, Path;

(SQL Fiddle)

Answer 2

It appears that using Sort Order in any combination just to accommodate a particular level is just not going to cut it in this instance. I believe what you are looking for is an in-order tree traversal.

I have already written posts on how to do pre-order tree traversal

• Dec 10, 2012 : MySQL: Tree-Hierarchical query
• Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs

You should be able to pull this off using id,parent_id,sort_order and path.

In fact, let me describe a pseudo-algorithm.

• DATA STRUCTURE : Stack
• STACK OPERATIONS
  • Push (Place New Data on Top of Stack)
  • Pop (Remove Data From Top of Stack)
• ALGORITHM START : Push top parent_id (0)
• LOOP
  • Pop parent_id from the Stack
  • Display label of parent_id indented based on level
  • Retrieve All id values from parent_id whose status = 1
  • Push All Retrieved id values by sort_order DESC, path DESC into the Stack
• END LOOP when Stack is Empty after Pop with nothing to Push

I will leave it to you write the Stored Procedures for handling a Stack.