Laravel recursive geographic data bites the moment your regions go more than one level deep. A country has divisions, a division has districts, a district has zones, and then someone asks you to subdivide a zone into a grid of cells and find which cell a GPS point falls in. If you model this with a naive parent_id and walk it in PHP, fetching one district's full descendant tree turns into a recursion of one query per level — and rendering a sidebar of the whole hierarchy becomes the classic N+1 storm. The fix is to stop walking the tree row by row: pick a storage model built for read-heavy descendant queries (materialized path or a recursive CTE), and turn point-in-cell lookups into a bounded range scan on an index instead of a table scan.
Which storage model: adjacency list, nested set, or materialized path?
There are three classic ways to store a tree in a relational database, and the right choice depends entirely on your read/write ratio. Geographic hierarchies are almost pure reads — Bangladesh's divisions and districts do not change between deploys — so optimize for fetching subtrees fast, not for cheap inserts.
- Adjacency list (parent_id only): trivial to write, and a single node's direct parent or children is one query. The problem is depth — fetching a full subtree means one query per level (or a recursive PHP loop), which is exactly the recursion you are trying to kill.
- Nested set (lft/rgt bounds): a whole subtree is one range query (WHERE lft BETWEEN x AND y), which is genuinely fast to read. But every insert or move rewrites the lft/rgt values of half the table inside a transaction — brutal if the hierarchy is ever edited, and easy to corrupt under concurrency.
- Materialized path (a path column like 1/4/): each row stores its ancestry as a string. Descendants are a single indexed LIKE '1/4/%', ancestors are parsed straight out of the path with no query at all, and inserts only touch the new row. This is my default for region trees.
My recommendation: store both parent_id and a materialized path. The parent_id keeps referential integrity and makes direct-children relations clean in Eloquent; the path makes descendant and ancestor reads cheap. If your database engine supports it (MySQL 8.0+, PostgreSQL, MariaDB 10.2+), you also get recursive CTEs — so you can keep a plain adjacency list and still fetch an entire subtree in one statement when you need the dynamic version.
What does the migration look like?
Here is the regions table. parent_id is a self-referencing nullable foreign key, path is the materialized path of ancestor ids (excluding the row itself), and depth lets you filter by level (0 = country, 1 = division, and so on) without parsing the string. The index on path is what makes the descendant LIKE query fast — without it you are back to a full table scan, which is the same indexing discipline I cover in my database indexing guide.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
public function up(): void
{
Schema::create('regions', function (Blueprint $table) {
$table->id();
$table->foreignId('parent_id')
->nullable()
->constrained('regions')
->nullOnDelete();
$table->string('name');
$table->string('type', 32); // country|division|district|zone
$table->unsignedTinyInteger('depth')->default(0);
// Materialized path of ancestor ids, excluding this row.
// A district under country 1, division 4 has path "1/4/".
// The trailing slash makes LIKE prefixes unambiguous.
$table->string('path', 512)->index();
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('regions');
}
};
Keep the trailing slash on every path, and build the descendant pattern as path + id + '/%' rather than path + id + '%'. Without the slash, the prefix for district 19 (LIKE '1/4/19%') would also swallow ids like 190 and 191 once your ids cross into more digits. With the slash (LIKE '1/4/19/%') the segment boundary is unambiguous.
How do I fetch a whole subtree in one query?
If you have the path column, descendants are a one-liner — no recursion at all. This is the version I reach for first because it is portable and uses the index directly.
// All descendants of a district (its zones and everything below), one indexed query.
$district = Region::where('type', 'district')->firstOrFail();
// path holds ancestors only, so append the district's own id to scope to it.
$descendants = Region::query()
->where('path', 'like', $district->path . $district->id . '/%')
->orderBy('path')
->get();
// Direct ancestors come straight out of the path string — zero extra queries.
$ancestorIds = array_filter(explode('/', rtrim($district->path, '/')));
$ancestors = Region::whereIn('id', $ancestorIds)->orderBy('depth')->get();When you do not maintain a path column — or the tree is edited often and you do not want to recompute paths — lean on a recursive CTE. This runs the traversal inside the engine and returns the full subtree in a single round trip. The syntax below is standard SQL that works unchanged on MySQL 8.0+, MariaDB 10.2+, and PostgreSQL (handy if you are still weighing the engines — see MySQL vs PostgreSQL).
use Illuminate\Support\Facades\DB;
$rootId = 19; // a district id
$subtree = DB::select(<<<'SQL'
WITH RECURSIVE region_tree AS (
-- Anchor: the starting node
SELECT id, parent_id, name, type, depth
FROM regions
WHERE id = ?
UNION ALL
-- Recursive step: join children back onto the working set
SELECT r.id, r.parent_id, r.name, r.type, r.depth
FROM regions r
INNER JOIN region_tree rt ON r.parent_id = rt.id
)
SELECT * FROM region_tree ORDER BY depth, name;
SQL, [$rootId]);If fetching a region's descendants costs you one query per level, you do not have a tree — you have an N+1 generator with extra steps.
How do I subdivide a zone into a grid and find which cell a point is in?
Once you reach the leaf level, you often need finer granularity than the hierarchy gives you — delivery zones, coverage cells, sensor buckets. The trap is to store each cell as a polygon and run a point-in-polygon test across the table for every lookup; that is a scan. Instead, subdivide the zone's bounding box into a fixed grid and store each cell's bounds (min/max lat-lng). Point-in-cell then becomes a bounded range lookup the database can satisfy from an index.
Schema::create('grid_cells', function (Blueprint $table) {
$table->id();
$table->foreignId('region_id')->constrained('regions')->cascadeOnDelete();
$table->decimal('min_lat', 9, 6);
$table->decimal('max_lat', 9, 6);
$table->decimal('min_lng', 9, 6);
$table->decimal('max_lng', 9, 6);
$table->string('quadkey', 32)->nullable(); // optional: tile/quadkey for prefix lookups
$table->timestamps();
// Composite index: equality on region_id, then a range on the lat bounds.
$table->index(['region_id', 'min_lat', 'max_lat', 'min_lng', 'max_lng'], 'grid_cells_bounds_idx');
$table->index('quadkey');
});The point-in-cell query is then a plain bounded comparison. Because the composite index leads with region_id and then min_lat, the engine narrows to one region by equality and seeks into the lat range rather than reading the whole table:
$lat = 23.810331;
$lng = 90.412521;
$cell = GridCell::where('region_id', $zoneId)
->where('min_lat', '<=', $lat)->where('max_lat', '>=', $lat)
->where('min_lng', '<=', $lng)->where('max_lng', '>=', $lng)
->first();Two scaling notes. First, if you store a geohash or Bing-Maps-style quadkey per cell, point-in-cell collapses further: encode the point to the same precision and look up the quadkey by exact match or prefix — no range comparison at all, and prefixes let you query a whole parent tile in one LIKE. Second, for true polygon zones rather than rectangular cells, reach for the database's native spatial types (MySQL POINT/POLYGON with a SPATIAL INDEX and ST_Contains, or PostGIS) — but keep the rectangular grid as a coarse pre-filter so the expensive geometry test only runs on the handful of candidates inside the bounding box.
How do I render the whole tree without N+1 in Eloquent?
For direct relations, define the self-referencing children and parent on the model. The mistake that quietly kills performance is recursing into ->children on each node while rendering a view — that fires a fresh query per node. Eager-load instead. Laravel lets you nest the children relation as deep as your tree goes in a single call.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
class Region extends Model
{
protected $fillable = ['parent_id', 'name', 'type', 'depth', 'path'];
public function parent(): BelongsTo
{
return $this->belongsTo(Region::class, 'parent_id');
}
public function children(): HasMany
{
return $this->hasMany(Region::class, 'parent_id');
}
// Convenience: recursively eager-load children for tree rendering.
public function descendants(): HasMany
{
return $this->children()->with('descendants');
}
}
// Country roots with four levels eager-loaded — a bounded number of queries, not one per node.
$tree = Region::query()
->whereNull('parent_id')
->with('children.children.children') // division -> district -> zone
->get();
// Or, for an arbitrary-depth tree, the recursive relation loads it in one nested pass:
$root = Region::with('descendants')->find($countryId);The recursive descendants relation issues one query per depth level — Eloquent batches all nodes at the same level into a single whereIn — so a four-level country tree is four queries total regardless of how many districts and zones it holds, not one per district. That is the whole game: bounded queries, not per-node ones. For very large or frequently rendered trees, cache the assembled structure rather than rebuilding it each request; I walk through the caching patterns in my Laravel performance optimization guide.
Geographic hierarchies look innocent until the data goes deep and the traffic goes up. Store parent_id for integrity, a materialized path for cheap descendant and ancestor reads, and lean on a recursive CTE when you need the dynamic, no-maintenance version — all three are correct tools for different read/write profiles. For the grid, store cell bounds (or a quadkey) and index them so point-in-cell is a range scan, and keep spatial polygon tests behind a rectangular pre-filter. Do that and the operations that used to fan out into dozens of queries collapse into one or a handful — which is the difference between a region picker that renders instantly and one that times out the day your map fills with real data.
