Need some help in getting a collection, using Laravel Eloquent, from a table but ordered by the parent_id to achieve, as an example, the following hierarchy:
(...) etc.
The below table is what I have and it's ordered by parent_id only.
What I'm trying to achieve is the parent > children ordering. Thanks in advance for any help.
+----+---------------------------------+-----------+
| id | region_common_name | parent_id |
+----+---------------------------------+-----------+
| 1 | Global | 0 |
| 2 | Africa | 1 |
| 14 | Americas | 1 |
| 33 | Antartica | 1 |
| 3 | Asia | 1 |
| 6 | Australasia | 1 |
| 4 | Europe | 1 |
| 10 | Eastern Africa | 2 |
| 11 | Middle Africa | 2 |
| 8 | Northern Africa | 2 |
| 12 | Southern Africa | 2 |
| 9 | Sub-Saharan Africa | 2 |
| 13 | Western Africa | 2 |
| 20 | Central Asia | 3 |
| 21 | Eastern Asia | 3 |
| 22 | South-eastern Asia | 3 |
| 23 | Southern Asia | 3 |
| 24 | Western Asia | 3 |
| 25 | Eastern Europe | 4 |
| 26 | Northern Europe | 4 |
| 27 | Southern Europe | 4 |
| 28 | Western Europe | 4 |
| 16 | Caribbean | 5 |
| 17 | Central America | 5 |
| 15 | Latin America and the Caribbean | 5 |
| 19 | Northern America | 5 |
| 29 | Australia and New Zealand | 6 |
| 30 | Melanesia | 6 |
| 31 | Micronesia | 6 |
| 32 | Polynesia | 6 |
| 5 | North America | 14 |
| 18 | South America | 14 |
| 7 | Southern America | 18 |
+----+---------------------------------+-----------+
You can order by multiple conditions but have to join on yourself.
Example in SQL
SELECT rp.region_common_name parent_name, r.region_common_name FROM regions r
INNER JOIN regions rp ON r.parent_id = rp.id
WHERE r.parent_id != 1 AND r.parent_id != 0
ORDER BY rp.region_common_name ACS, r.region_common_name ASC
So your result will look like this:
+---------------------------------+---------------------------------+
| parent_name | region_common_name |
+---------------------------------+---------------------------------+
| Africa | Eastern Africa |
| Africa | Middle Africa |
| Americas | North America |
| Americas | South America |
+---------------------------------+---------------------------------+
In eloquent you can use the QueryBuilder to generate your query. It will look like this:
DB::table('regions as r')
->join('regions as rp', 'r.parent_id', '=', 'rp.id')
->where('r.parent_id', '!=', '0')
->where('r.parent_id', '!=', '1')
->orderBy('rp.region_common_name', 'asc')
->orderBy('r.region_common_name', 'asc')
->select('rp.region_common_name as parent_name', 'r.region_common_name as name');