Created
October 10, 2017 10:29
-
-
Save mohanklein/1184091f467bba019892879998d0c31f to your computer and use it in GitHub Desktop.
Closure Table Trait for Laravel Eloquent Models
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace App\Models\Traits; | |
use Illuminate\Support\Facades\DB; | |
/** | |
* This trait keeps functionality to read & write hierarchical data from & into a closure table. | |
* IMPORTANT: In your model please set the variable $closure_table, e.g. | |
* protected $closure_table = 'destinations_tree'; | |
*/ | |
trait ClosureTable | |
{ | |
/** | |
* Returns all "older" relatives which are above. | |
* | |
* @return Illuminate\Database\Eloquent\Relations\BelongsToMany | |
*/ | |
public function ancestors() | |
{ | |
return $this->upwards()->where('ancestor', '!=', $this->id); | |
} | |
/** | |
* Returns the "oldest" relevant which is a tree's starting point. | |
* | |
* @return Illuminate\Database\Eloquent\Model | |
*/ | |
public function root() | |
{ | |
return $this->ancestors()->orderBy('depth', 'desc')->first(); | |
} | |
/** | |
* Returns all "younger" relevants which are underneath. | |
* | |
* @return Illuminate\Database\Eloquent\Relations\BelongsToMany | |
*/ | |
public function descendants() | |
{ | |
return $this->downwards()->where('descendant', '!=', $this->id); | |
} | |
/** | |
* Returns all upwards elements of a tree ("thicker branches") with the beginning node itself. | |
* | |
* @return Illuminate\Database\Eloquent\Relations\BelongsToMany | |
*/ | |
public function upwards() | |
{ | |
$query = $this->belongsToMany( | |
get_called_class(), | |
$this->closure_table, | |
'descendant', | |
'ancestor' | |
) | |
->orderBy('depth', 'asc'); | |
return $query; | |
} | |
/** | |
* Returns all downwards elements of a tree ("thinner branches") with the beginning node itself. | |
* | |
* @return Illuminate\Database\Eloquent\Relations\BelongsToMany | |
*/ | |
public function downwards() | |
{ | |
$query = $this->belongsToMany( | |
get_called_class(), | |
$this->closure_table, | |
'ancestor', | |
'descendant' | |
) | |
->orderBy('depth', 'asc'); | |
return $query; | |
} | |
/** | |
* Adds a "0"-depth row for a root entity. | |
* | |
* @return void | |
*/ | |
public function makeRoot() | |
{ | |
DB::table($this->closure_table)->insert( | |
[ | |
'ancestor' => $this->id, | |
'descendant' => $this->id, | |
'depth' => 0, | |
] | |
); | |
} | |
/** | |
* Decides whether to add a new parent node or move from an existing to another one. | |
* | |
* @param int $parent_id | |
* @return void | |
*/ | |
public function setParent(int $parent_id) | |
{ | |
if ($this->hasExistingParent()) { | |
$this->moveToParent($parent_id); | |
return; | |
} | |
$this->addToParent($parent_id); | |
} | |
/** | |
* Adds a new entity to the tree underneath a parent node. | |
* | |
* @param int $parent_id | |
* @return void | |
*/ | |
protected function addToParent(int $parent_id) | |
{ | |
$this->checkParentValidity($parent_id); | |
$sql = "INSERT INTO {$this->closure_table} (ancestor, descendant, depth) | |
SELECT t.ancestor, CAST(:object_id AS INTEGER), t.depth+1 | |
FROM {$this->closure_table} AS t | |
WHERE t.descendant = :parent_id | |
UNION ALL | |
SELECT CAST(:object_id AS INTEGER), CAST(:object_id AS INTEGER), 0"; | |
DB::connection($this->connection)->statement($sql, [ | |
'object_id' => $this->id, | |
'parent_id' => $parent_id, | |
]); | |
} | |
/** | |
* Moves an existing node and its descendants to a different parent. | |
* | |
* @param int $parent_id | |
* @return void | |
*/ | |
protected function moveToParent(int $parent_id) | |
{ | |
$this->checkParentValidity($parent_id); | |
// Delete all existing ancestor associations for the current entity | |
// and delete all associations between the current entity's children and ancestors above the current entity | |
$delete_sql = "DELETE FROM {$this->closure_table} | |
WHERE descendant IN ( | |
SELECT d FROM ( | |
SELECT descendant as d FROM {$this->closure_table} | |
WHERE ancestor = :object_id | |
) as dct | |
) | |
AND ancestor IN ( | |
SELECT a FROM ( | |
SELECT ancestor AS a FROM {$this->closure_table} | |
WHERE descendant = :object_id | |
AND ancestor <> :object_id | |
) as ct | |
)"; | |
// Write the associations for the new ancestors for the current entity and all its children | |
$insert_sql = "INSERT INTO {$this->closure_table} (ancestor, descendant, depth) | |
SELECT supertree.ancestor, subtree.descendant, supertree.depth+subtree.depth+1 | |
FROM {$this->closure_table} AS supertree | |
CROSS JOIN {$this->closure_table} AS subtree | |
WHERE subtree.ancestor = :object_id | |
AND supertree.descendant = :parent_id"; | |
DB::beginTransaction(); | |
try { | |
DB::connection($this->connection)->statement($delete_sql, [ | |
'object_id' => $this->id, | |
]); | |
DB::connection($this->connection)->statement($insert_sql, [ | |
'object_id' => $this->id, | |
'parent_id' => $parent_id, | |
]); | |
DB::commit(); | |
} catch (\Exception $e) { | |
DB::rollback(); | |
throw new \Exception('Couldn\'t change the parent id. Transaction was rolled back! Details: ' . $e->getMessage()); | |
} | |
} | |
/** | |
* Checks if a parent id is an existing node. | |
* | |
* @param int $parent_id | |
* @return null | \Exception | |
*/ | |
protected function checkParentValidity(int $parent_id) | |
{ | |
if (DB::table("{$this->closure_table}") | |
->where('ancestor', $parent_id) | |
->count() < 1) | |
{ | |
throw new \Exception('No ancestor row existing for this parent id!'); | |
} | |
} | |
/** | |
* Checks if an entity already has a parent node record. | |
* | |
* @param int $parent_id | |
* @return boolean | |
*/ | |
protected function hasExistingParent() | |
{ | |
return DB::table("{$this->closure_table}") | |
->where('descendant', $this->id) | |
->where('ancestor', '!=', $this->id) | |
->count() > 0; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment