Geek asks and answers

Sort children after their parent

I am trying to implement a category table. The description of the simplified table looks like this

id -- name -- parent_id

assuming example data for example

id - name - parent_id
1 test1  null
2 test2  null
3 test3  null
4 test4  1
5 test5  4
6 test6  2
7 test7  1

I am trying to come up with a sql query that will return a record in the following order

id - name - parent_id
1 test1  null
4 test4  1
5 test5  4
7 test7  1
2 test2  null
6 test6  2
3 test3  null

Basically, children are returned after their parent.

----------------------- SOLVED BY USING LINQ / recursion in code ------------- ------- -----

Not really a sql solution, but it ultimately works.

+3
source share
4 answers

, , . , . , , . , , , . , , , , .

+2

, , ( SQL, ).

categories
 - category_id  |  int(11)  | Primary Key / Auto_Increment
 ..
 ..

sub_categories
 - sub_category_id  |  int(11)  |  Primary Key / Auto_Increment
 - category_id      |  int(11)  |  Foreign Key to categories table
 ..
 ..
+1

order_parent, , , , . order_parent, , parent_id null ( ) .

:

  • , , .
  • nulls parent_id last, DESC.

, !

SELECT   id,
         name,
         parent_id,
         (
             case
             when parent_id is null then id
             else parent_id
             end
         ) AS order_parent
FROM     myTable
ORDER BY order_parent, parent_id
+1

:

SELECT id, name, parent_id, (CASE WHEN COALESCE(parentid,0)=0 THEN id ELSE (parentid + '.' + id)) as orderid
FROM table
ORDER BY (CASE WHEN COALESCE(parentid,0)=0 THEN id ELSE (parentid + '.' + id))

This should create a new column called orderid that has a parent id point (1.4, 4.5, etc.). For columns where the parent id is null, it will only indicate the id. Thus, you will receive the order as 1, 1.4, 4, 4.5, etc.

Please check the code since I wrote this on the fly without testing. He must be close.

+1
source

More articles:


All Articles