<p>I am assuming there is only a generation, so I want to order the result from a db table with (id,parent_id) structure implementing the parent-child internal relationship, ie:</p>
<p> </p>
<table style="width: 500px; clear: both;" border="0" align="left">
<caption><span>Table name: </span><em><strong>catogories</strong></em></caption>
<thead>
<tr>
<td><strong>id</strong></td>
<td><strong>parent_id</strong></td>
<td><strong>name</strong></td>
</tr>
</thead>
<tbody>
<tr>
<td>int</td>
<td>int</td>
<td>varchar(100)</td>
</tr>
</tbody>
</table>
<p> </p>
<p> </p>
<p> </p>
<p>So, I found some hint <a href="http://stackoverflow.com/questions/8022158/how-do-i-order-by-parent-then-child" target="_blank">here</a>, and <a href="http://stackoverflow.com/questions/24599453/sql-order-by-parent-child-and-sort-order" target="_blank">here</a>, but actually I did wrote my solution first using IF in SQL:</p>
<pre class="brush:sql;first-line:1;">SELECT c.*, IF(c.parent_id = 0, c.id*1000, c.parent_id*1000+c.id) as sorter FROM categories as c ORDER BY sorter
</pre>
<p>And for <strong>one generation</strong> structure, it works. Results are something like:</p>
<table style="width: 600px;" border="0">
<caption>results</caption>
<thead>
<tr>
<td><strong>id</strong></td>
<td><strong>parent_id</strong></td>
<td><strong>name</strong></td>
<td><strong>sorter</strong></td>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>0</td>
<td>Fruits</td>
<td>1001</td>
</tr>
<tr>
<td>3</td>
<td>1</td>
<td>Banana</td>
<td>1003</td>
</tr>
<tr>
<td>2</td>
<td>0</td>
<td>Vegetables</td>
<td>2002</td>
</tr>
</tbody>
</table>
<p> </p>
<p>That look simple enough</p>
<p> </p>
<p>Note on image source: flickr vivariphotography, CC, commercial use allowed, <a href="https://www.flickr.com/photos/vivariphotography/5239422637/in/photolist-8YZpP4-aEVRXP-8tjn4j-vTJqt-oLcBwi-okSvMS-8bQ8j4-TqnAV-cf1vwQ-SqPqV-6GhMfK-9GQSmN-vH4L1K-c8b77Y-vqQGaS-5LztEk-8xBMES-5Ez63Q-pb1Hqt-4UmgpE-cm83dU-o3dM6U-6JMMkb-8ToJaP-c8b6Hd-v2WnpA-5uVuJm-7d4XnB-2e7qPz-fp6cps-fZfH6k-d2kRsq-d2kS1d-d2kSyS-92SDP-vntEBD-vmB8dh-v5FLQ6-v5yjed-m796hX-najybv-943X1n-iMo4np-d2kGJo-d2p51h-d2p5C3-d2p3zQ-d2p3PY-d2p483-d2p6iq" target="_blank">link</a></p>
I am assuming there is only a generation, so I want to order the result from a db table with (id,parent_id) structure implementing the parent-child internal relationship, ie:
Table name: catogories
| id |
parent_id |
name |
| int |
int |
varchar(100) |
So, I found some hint here, and here, but actually I did wrote my solution first using IF in SQL:
SELECT c.*, IF(c.parent_id = 0, c.id*1000, c.parent_id*1000+c.id) as sorter FROM categories as c ORDER BY sorter
And for one generation structure, it works. Results are something like:
results
| id |
parent_id |
name |
sorter |
| 1 |
0 |
Fruits |
1001 |
| 3 |
1 |
Banana |
1003 |
| 2 |
0 |
Vegetables |
2002 |
That look simple enough
Note on image source: flickr vivariphotography, CC, commercial use allowed, link
