riza
app/Models/Organization.php
public function children(): HasMany
{
return $this->hasMany(Organization::class)
->with('children');
}
app/Models/User.php
public function organizations(): HasMany
{
return $this->hasMany(Organization::class);
}
İlişki bu. Eğer $user->organizations()->with('children')->get() yaparsanız tüm ilişkiyi çekersiniz fakat her bir organizasyon için bir sorgu demek. Bu yapıda bunu aşmanın kolay yolu yok, derinlik hesaplaması ile sınırlandırma ya da önbellek vs kullanmanız gerekecek.
organizations
+----+---------+---------+-----------+
| id | name | user_id | parent_id |
+----+---------+---------+-----------+
| 1 | Tekil | 1 | null |
| 2 | A | 1 | null |
| 3 | A-1 | 1 | 2 |
| 4 | A-2 | 1 | 2 |
| 5 | A-1-1 | 1 | 3 |
| 6 | A-1-2 | 1 | 3 |
| 7 | A-1-1-2 | 1 | 5 |
+----+---------+---------+-----------+
$user->organizations()->with('children')->get()
[
{
"id": 1,
"name": "Tekil",
"user_id": 1,
"parent_id": null,
"children": []
},
{
"id": 2,
"name": "A",
"user_id": 1,
"parent_id": null,
"children": [
{
"id": 3,
"name": "A-1",
"user_id": 1,
"parent_id": 2,
"children": [
{
"id": 5,
"name": "A-1-1",
"user_id": 1,
"parent_id": 3,
"children": [
{
"id": 7,
"name": "A-1-1-2",
"user_id": 1,
"parent_id": 5,
"children": []
}
]
},
{
"id": 6,
"name": "A-1-2",
"user_id": 1,
"parent_id": 3,
"children": []
}
]
},
{
"id": 4,
"name": "A-2",
"user_id": 1,
"parent_id": 2,
"children": []
}
]
},
{
"id": 3,
"name": "A-1",
"user_id": 1,
"parent_id": 2,
"children": [
{
"id": 5,
"name": "A-1-1",
"user_id": 1,
"parent_id": 3,
"children": [
{
"id": 7,
"name": "A-1-1-2",
"user_id": 1,
"parent_id": 5,
"children": []
}
]
},
{
"id": 6,
"name": "A-1-2",
"user_id": 1,
"parent_id": 3,
"children": []
}
]
},
{
"id": 4,
"name": "A-2",
"user_id": 1,
"parent_id": 2,
"children": []
},
{
"id": 5,
"name": "A-1-1",
"user_id": 1,
"parent_id": 3,
"children": [
{
"id": 7,
"name": "A-1-1-2",
"user_id": 1,
"parent_id": 5,
"children": []
}
]
},
{
"id": 6,
"name": "A-1-2",
"user_id": 1,
"parent_id": 3,
"children": []
},
{
"id": 7,
"name": "A-1-1-2",
"user_id": 1,
"parent_id": 5,
"children": []
}
]
Bu üstteki sorgu SELECT count(id) - 1 FROM organizations WHERE parent_id IS NOT NULL; kadar sorgu oluşturuyor, yani şunları:
SELECT * FROM organizations WHERE parent_id IN (1, 2, 3, 4, 5, 6, 7);
SELECT * FROM organizations WHERE parent_id IN (3, 4, 5, 6, 7);
SELECT * FROM organizations WHERE parent_id IN (5, 6, 7);
SELECT * FROM organizations WHERE parent_id IN (7);
Kategori sayısı arrtıkça sorgu sayısı da artacak. Onun yerine şöyle bir şey yapabilirsiniz:
app/Models/User.php
public function organizationTree($parentId = null)
{
$tree = collect();
$this->organizations->each(function ($organization) use (&$tree, $parentId) {
if ($organization->parent_id == $parentId) {
$children = $this->organizationTree($organization->id);
if ($children) {
$organization->setAttribute('children', $children);
}
$tree->push($organization);
}
});
return $tree;
}
Bu test ettiğinizde ise:
$user->organizationTree();
[
{
"id": 1,
"name": "Tekil",
"user_id": 1,
"parent_id": null,
"children": []
},
{
"id": 2,
"name": "A",
"user_id": 1,
"parent_id": null,
"children": [
{
"id": 3,
"name": "A-1",
"user_id": 1,
"parent_id": 2,
"children": [
{
"id": 5,
"name": "A-1-1",
"user_id": 1,
"parent_id": 3,
"children": [
{
"id": 7,
"name": "A-1-1-2",
"user_id": 1,
"parent_id": 5,
"children": []
}
]
},
{
"id": 6,
"name": "A-1-2",
"user_id": 1,
"parent_id": 3,
"children": []
}
]
},
{
"id": 4,
"name": "A-2",
"user_id": 1,
"parent_id": 2,
"children": []
}
]
}
]
Bu ise tek sorguda işi halletti:
SELECT * FROM organizations WHERE user_id = ? AND user_id IS NOT NULL;
Veritabanı düzeyinde araştırma yapmak isterseniz ise:
https://www.postgresql.org/docs/current/ltree.html
https://www.postgresql.org/docs/current/queries-with.html
https://dev.mysql.com/doc/refman/8.0/en/with.html