QueryBuilder::rebuildTree() - Code Metrics - Inspection of "Fixes + Refactoring" - ARCANEDEV/LaravelNestedSet - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#1)

by ARCANEDEV

created 2016-03-07 14:52 UTC

QueryBuilder::rebuildTree() A

↳ Parent: QueryBuilder

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
c	2
b	0
f	0
dl	0
loc	6
ccs	3
cts	3
cp	1
rs	9.4285
cc	1
eloc	3
nc	1
nop	2
crap	1

<?php namespace Arcanedev\LaravelNestedSet\Eloquent;

use Arcanedev\LaravelNestedSet\Utilities\NestedSet;
use Arcanedev\LaravelNestedSet\Utilities\TreeHelper;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Illuminate\Database\Query\Builder as Query;
use Illuminate\Database\Query\Expression;
use LogicException;

/**
 * Class     QueryBuilder
 *
 * @package  Arcanedev\LaravelNestedSet\Eloquent
 * @author   ARCANEDEV <[email protected]>
 *
 * @method  static  \Arcanedev\LaravelNestedSet\Eloquent\QueryBuilder  whereIn(string $column, mixed $values, string $boolean = 'and', bool $not = false)
 */
class QueryBuilder extends Builder
{
    /* ------------------------------------------------------------------------------------------------
     |  Properties
     | ------------------------------------------------------------------------------------------------
     */
    /**
     * The model being queried.
     *
     * @var \Arcanedev\LaravelNestedSet\Contracts\Nodeable
     */
    protected $model;

    /* ------------------------------------------------------------------------------------------------
     |  Main Functions
     | ------------------------------------------------------------------------------------------------
     */
    /**
     * Get node's `lft` and `rgt` values.
     *
     * @param  mixed  $id
     * @param  bool   $required
     *
     * @return array
     */
    public function getNodeData($id, $required = false)
    {
        $query = $this->toBase();

        $query->where($this->model->getKeyName(), '=', $id);

        $data  = $query->first([
            $this->model->getLftName(),
            $this->model->getRgtName(),
        ]);

        if ( ! $data && $required) {
            throw new ModelNotFoundException;
        }

        return (array) $data;
    }

    /**
     * Get plain node data.
     *
     * @param  mixed  $id
     * @param  bool   $required
     *
     * @return array
     */
    public function getPlainNodeData($id, $required = false)
    {
        return array_values($this->getNodeData($id, $required));
    }

    /**
     * Scope limits query to select just root node.
     *
     * @return self
     */
    public function whereIsRoot()
    {
        $this->query->whereNull($this->model->getParentIdName());

        return $this;
    }

    /**
     * Limit results to ancestors of specified node.
     *
     * @param  mixed  $id
     *
     * @return self
     */
    public function whereAncestorOf($id)
    {
        $keyName = $this->model->getKeyName();

        if (NestedSet::isNode($id)) {
            $value = '?';

            $this->query->addBinding($id->getLft());

            $id = $id->getKey();
        } else {
            $valueQuery = $this->model
                ->newQuery()
                ->toBase()
                ->select("_.".$this->model->getLftName())
                ->from($this->model->getTable().' as _')
                ->where($keyName, '=', $id)
                ->limit(1);

            $this->query->mergeBindings($valueQuery);

            $value = '(' . $valueQuery->toSql() . ')';
        }

        list($lft, $rgt) = $this->wrappedColumns();

        $this->query->whereRaw("{$value} between {$lft} and {$rgt}");

        // Exclude the node
        $this->where($keyName, '<>', $id);

        return $this;
    }

    /**
     * Get ancestors of specified node.
     *
     * @param  mixed  $id
     * @param  array  $columns
     *
     * @return self
     */
    public function ancestorsOf($id, array $columns = ['*'])
    {
        return $this->whereAncestorOf($id)->get($columns);
    }

    /**
     * Add node selection statement between specified range.
     *
     * @param  array   $values
     * @param  string  $boolean
     * @param  bool    $not
     *
     * @return self
     */
    public function whereNodeBetween($values, $boolean = 'and', $not = false)
    {
        $this->query->whereBetween($this->model->getLftName(), $values, $boolean, $not);

        return $this;
    }

    /**
     * Add node selection statement between specified range joined with `or` operator.
     *
     * @param  array  $values
     *
     * @return self
     */
    public function orWhereNodeBetween($values)
    {
        return $this->whereNodeBetween($values, 'or');
    }

    /**
     * Add constraint statement to descendants of specified node.
     *
     * @param  mixed   $id
     * @param  string  $boolean
     * @param  bool    $not
     *
     * @return self
     */
    public function whereDescendantOf($id, $boolean = 'and', $not = false)
    {
        $data = NestedSet::isNode($id)
            ? $id->getBounds()
            : $this->model->newNestedSetQuery()->getPlainNodeData($id, true);

        // Don't include the node
        ++$data[0];

        return $this->whereNodeBetween($data, $boolean, $not);
    }

    /**
     * @param  mixed  $id
     *
     * @return self
     */
    public function whereNotDescendantOf($id)
    {
        return $this->whereDescendantOf($id, 'and', true);
    }

    /**
     * @param  mixed  $id
     *
     * @return self
     */
    public function orWhereDescendantOf($id)
    {
        return $this->whereDescendantOf($id, 'or');
    }

    /**
     * @param  mixed  $id
     *
     * @return self
     */
    public function orWhereNotDescendantOf($id)
    {
        return $this->whereDescendantOf($id, 'or', true);
    }

    /**
     * Get descendants of specified node.
     *
     * @param  mixed  $id
     * @param  array  $columns
     *
     * @return \Arcanedev\LaravelNestedSet\Eloquent\Collection
     */
    public function descendantsOf($id, array $columns = ['*'])
    {
        try {
            return $this->whereDescendantOf($id)->get($columns);
        }
        catch (ModelNotFoundException $e) {
            return $this->model->newCollection();
        }
    }

    /**
     * @param  mixed   $id
     * @param  string  $operator
     * @param  string  $boolean
     *
     * @return self
     */
    protected function whereIsBeforeOrAfter($id, $operator, $boolean)
    {
        if (NestedSet::isNode($id)) {
            $value = '?';

            $this->query->addBinding($id->getLft());
        } else {
            $valueQuery = $this->model
                ->newQuery()
                ->toBase()
                ->select('_n.'.$this->model->getLftName())
                ->from($this->model->getTable().' as _n')
                ->where('_n.'.$this->model->getKeyName(), '=', $id);

            $this->query->mergeBindings($valueQuery);

            $value = '('.$valueQuery->toSql().')';
        }

        list($lft,) = $this->wrappedColumns();

        $this->query->whereRaw("{$lft} {$operator} {$value}", [ ], $boolean);

        return $this;
    }

    /**
     * Constraint nodes to those that are after specified node.
     *
     * @param  mixed   $id
     * @param  string  $boolean
     *
     * @return self
     */
    public function whereIsAfter($id, $boolean = 'and')
    {
        return $this->whereIsBeforeOrAfter($id, '>', $boolean);
    }

    /**
     * Constraint nodes to those that are before specified node.
     *
     * @param  mixed   $id
     * @param  string  $boolean
     *
     * @return self
     */
    public function whereIsBefore($id, $boolean = 'and')
    {
        return $this->whereIsBeforeOrAfter($id, '<', $boolean);
    }

    /**
     * Include depth level into the result.
     *
     * @param  string  $as
     *
     * @return self
     */
    public function withDepth($as = 'depth')
    {
        if ($this->query->columns === null) {
            $this->query->columns = ['*'];
        }

        $table = $this->wrappedTable();

        list($lft, $rgt) = $this->wrappedColumns();

        $query = $this->model
            ->newScopedQuery('_d')
            ->toBase()
            ->selectRaw('count(1) - 1')
            ->from($this->model->getTable().' as _d')
            ->whereRaw("{$table}.{$lft} between _d.{$lft} and _d.{$rgt}");

        $this->query->selectSub($query, $as);

        return $this;
    }

    /**
     * Get wrapped `lft` and `rgt` column names.
     *
     * @return array
     */
    protected function wrappedColumns()
    {
        $grammar = $this->query->getGrammar();

        return [
            $grammar->wrap($this->model->getLftName()),
            $grammar->wrap($this->model->getRgtName()),
        ];
    }

    /**
     * Get a wrapped table name.
     *
     * @return string
     */
    protected function wrappedTable()
    {
        return $this->query->getGrammar()->wrapTable($this->getQuery()->from);
    }

    /**
     * Wrap model's key name.
     *
     * @return string
     */
    protected function wrappedKey()
    {
        return $this->query->getGrammar()->wrap($this->model->getKeyName());
    }

    /**
     * Exclude root node from the result.
     *
     * @return self
     */
    public function withoutRoot()
    {
        $this->query->whereNotNull($this->model->getParentIdName());

        return $this;
    }

    /**
     * Order by node position.
     *
     * @param  string  $dir
     *
     * @return self
     */
    public function defaultOrder($dir = 'asc')
    {
        $this->query->orders = [];
        $this->query->orderBy($this->model->getLftName(), $dir);

        return $this;
    }

    /**
     * Order by reversed node position.
     *
     * @return self
     */
    public function reversed()
    {
        return $this->defaultOrder('desc');
    }

    /**
     * Move a node to the new position.
     *
     * @param  mixed  $key
     * @param  int    $position
     *
     * @return int
     */
    public function moveNode($key, $position)
    {
        list($lft, $rgt) = $this->model->newNestedSetQuery()
                                       ->getPlainNodeData($key, true);

        if ($lft < $position && $position <= $rgt) {
            throw new LogicException('Cannot move node into itself.');
        }

        // Get boundaries of nodes that should be moved to new position
        $from = min($lft, $position);
        $to   = max($rgt, $position - 1);

        // The height of node that is being moved
        $height   = $rgt - $lft + 1;

        // The distance that our node will travel to reach it's destination
        $distance = $to - $from + 1 - $height;

        // If no distance to travel, just return
        if ($distance === 0) {
            return 0;
        }

        if ($position > $lft) {
            $height *= -1;
        } else {
            $distance *= -1;
        }

        $boundary = [$from, $to];
        $query    = $this->toBase()->where(function (Query $inner) use ($boundary) {
            $inner->whereBetween($this->model->getLftName(), $boundary);
            $inner->orWhereBetween($this->model->getRgtName(), $boundary);
        });

        return $query->update($this->patch(
            compact('lft', 'rgt', 'from', 'to', 'height', 'distance')
        ));
    }

    /**
     * Make or remove gap in the tree. Negative height will remove gap.
     *
     * @param  int  $cut
     * @param  int  $height
     *
     * @return int
     */
    public function makeGap($cut, $height)
    {
        $query = $this->toBase()->whereNested(function (Query $inner) use ($cut) {
            $inner->where($this->model->getLftName(), '>=', $cut);
            $inner->orWhere($this->model->getRgtName(), '>=', $cut);
        });

        return $query->update($this->patch(
            compact('cut', 'height')
        ));
    }

    /**
     * Get patch for columns.
     *
     * @param  array  $params
     *
     * @return array
     */
    protected function patch(array $params)
    {
        $grammar = $this->query->getGrammar();
        $columns = [];

        foreach ([$this->model->getLftName(), $this->model->getRgtName()] as $col) {
            $columns[$col] = $this->columnPatch($grammar->wrap($col), $params);
        }

        return $columns;
    }

    /**
     * Get patch for single column.
     *
     * @param  string  $col
     * @param  array   $params
     *
     * @return string
     */
    protected function columnPatch($col, array $params)
    {
        /**
         * @var int $height
         * @var int $distance
         * @var int $lft
         * @var int $rgt
         * @var int $from
         * @var int $to
         */
        extract($params);

        if ($height > 0) $height = '+'.$height;

        if (isset($cut)) {
            return new Expression("case when {$col} >= {$cut} then {$col}{$height} else {$col} end");
        }

        if ($distance > 0) {
            $distance = '+'.$distance;
        }

        return new Expression(
            "case ".
            "when {$col} between {$lft} and {$rgt} then {$col}{$distance} ". // Move the node
            "when {$col} between {$from} and {$to} then {$col}{$height} ". // Move other nodes
            "else {$col} end"
        );
    }

    /**
     * Get statistics of errors of the tree.
     *
     * @return array
     */
    public function countErrors()
    {
        $checks = [
            'oddness'        => $this->getOddnessQuery(),      // Check if lft and rgt values are ok
            'duplicates'     => $this->getDuplicatesQuery(),   // Check if lft and rgt values are unique
            'wrong_parent'   => $this->getWrongParentQuery(),  // Check if parent_id is set correctly
            'missing_parent' => $this->getMissingParentQuery() // Check for nodes that have missing parent
        ];

        $query = $this->query->newQuery();

        foreach ($checks as $key => $inner) {
            /** @var \Illuminate\Database\Query\Builder $inner */
            $inner->selectRaw('count(1)');

            $query->selectSub($inner, $key);
        }

        return (array) $query->first();
    }

    /**
     * Get the oddness errors query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function getOddnessQuery()
    {
        return $this->model
            ->newNestedSetQuery()
            ->toBase()
            ->whereNested(function (Query $inner) {
                list($lft, $rgt) = $this->wrappedColumns();

                $inner->whereRaw("{$lft} >= {$rgt}")
                      ->orWhereRaw("({$rgt} - {$lft}) % 2 = 0");
            });
    }

    /**
     * Get the duplicates errors query.
     *
     * @return \Arcanedev\LaravelNestedSet\Eloquent\QueryBuilder|\Illuminate\Database\Query\Builder
     */
    protected function getDuplicatesQuery()
    {
        $table = $this->wrappedTable();

        $query = $this->model
            ->newNestedSetQuery('c1')
            ->toBase()
            ->from($this->query->raw("{$table} c1, {$table} c2"))
            ->whereRaw("c1.id < c2.id")
            ->whereNested(function (Query $inner) {
                list($lft, $rgt) = $this->wrappedColumns();

                $inner->orWhereRaw("c1.{$lft}=c2.{$lft}")
                      ->orWhereRaw("c1.{$rgt}=c2.{$rgt}")
                      ->orWhereRaw("c1.{$lft}=c2.{$rgt}")
                      ->orWhereRaw("c1.{$rgt}=c2.{$lft}");
            });

        return $this->model->applyNestedSetScope($query, 'c2');
    }

    /**
     * Get the wrong parent query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function getWrongParentQuery()
    {
        $table        = $this->wrappedTable();
        $keyName      = $this->wrappedKey();
        $parentIdName = $this->query->raw($this->model->getParentIdName());
        $query        = $this->model->newNestedSetQuery('c')
            ->toBase()
            ->from($this->query->raw("{$table} c, {$table} p, $table m"))
            ->whereRaw("c.{$parentIdName}=p.{$keyName}")
            ->whereRaw("m.{$keyName} <> p.{$keyName}")
            ->whereRaw("m.{$keyName} <> c.{$keyName}")
            ->whereNested(function (Query $inner) {
                list($lft, $rgt) = $this->wrappedColumns();

                $inner->whereRaw("c.{$lft} not between p.{$lft} and p.{$rgt}")
                      ->orWhereRaw("c.{$lft} between m.{$lft} and m.{$rgt}")
                      ->whereRaw("m.{$lft} between p.{$lft} and p.{$rgt}");
            });

        $this->model->applyNestedSetScope($query, 'p');
        $this->model->applyNestedSetScope($query, 'm');

        return $query;
    }

    /**
     * Get the missing parent query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function getMissingParentQuery()
    {
        return $this->model
            ->newNestedSetQuery()
            ->toBase()
            ->whereNested(function (Query $inner) {
                $table = $this->wrappedTable();
                $keyName = $this->wrappedKey();
                $parentIdName = $this->query->raw($this->model->getParentIdName());

                $query = $this->model
                    ->newNestedSetQuery()
                    ->toBase()
                    ->selectRaw('1')
                    ->from($this->query->raw("{$table} p"))
                    ->whereRaw("{$table}.{$parentIdName} = p.{$keyName}")
                    ->limit(1);

                $this->model->applyNestedSetScope($query, 'p');

                $inner->whereRaw("{$parentIdName} is not null")
                      ->addWhereExistsQuery($query, 'and', true);
            });
    }

    /**
     * Get the number of total errors of the tree.
     *
     * @return int
     */
    public function getTotalErrors()
    {
        return array_sum($this->countErrors());
    }

    /**
     * Get whether the tree is broken.
     *
     * @return bool
     */
    public function isBroken()
    {
        return $this->getTotalErrors() > 0;
    }

    /**
     * Fixes the tree based on parentage info.
     * Nodes with invalid parent are saved as roots.
     *
     * @return  int  The number of fixed nodes
     */
    public function fixTree()
    {
        $dictionary = $this->defaultOrder()
            ->get([
                $this->model->getKeyName(),
                $this->model->getParentIdName(),
                $this->model->getLftName(),
                $this->model->getRgtName(),
            ])
            ->groupBy($this->model->getParentIdName())
            ->all();

        return TreeHelper::fixNodes($dictionary);
    }

    /**
     * Rebuild the tree based on raw data.
     * If item data does not contain primary key, new node will be created.
     *
     * @param  array  $data
     * @param  bool   $delete  Whether to delete nodes that exists but not in the data array
     *
     * @return int
     */
    public function rebuildTree(array $data, $delete = false)
    {
        $existing = $this->get()->getDictionary();

        return TreeHelper::rebuild($data, $existing, $this->model, $delete);
    }

    /**
     * Get the root node.
     *
     * @param  array  $columns
     *
     * @return \Illuminate\Database\Eloquent\Model|\Arcanedev\LaravelNestedSet\Contracts\Nodeable|null
     */
    public function root(array $columns = ['*'])
    {
        return $this->whereIsRoot()->first($columns);
    }
}


1		<?php namespace Arcanedev\LaravelNestedSet\Eloquent;
2
3		use Arcanedev\LaravelNestedSet\Utilities\NestedSet;
4		use Arcanedev\LaravelNestedSet\Utilities\TreeHelper;
5		use Illuminate\Database\Eloquent\Builder;
6		use Illuminate\Database\Eloquent\ModelNotFoundException;
7		use Illuminate\Database\Query\Builder as Query;
8		use Illuminate\Database\Query\Expression;
9		use LogicException;
10
11		/**
12		* Class QueryBuilder
13		*
14		* @package Arcanedev\LaravelNestedSet\Eloquent
15		* @author ARCANEDEV <[email protected]>
16		*
17		* @method static \Arcanedev\LaravelNestedSet\Eloquent\QueryBuilder whereIn(string $column, mixed $values, string $boolean = 'and', bool $not = false)
18		*/
19		class QueryBuilder extends Builder
20		{
21		/* ------------------------------------------------------------------------------------------------
22		\| Properties
23		\| ------------------------------------------------------------------------------------------------
24		*/
25		/**
26		* The model being queried.
27		*
28		* @var \Arcanedev\LaravelNestedSet\Contracts\Nodeable
29		*/
30		protected $model;
31
32		/* ------------------------------------------------------------------------------------------------
33		\| Main Functions
34		\| ------------------------------------------------------------------------------------------------
35		*/
36		/**
37		* Get node's `lft` and `rgt` values.
38		*
39		* @param mixed $id
40		* @param bool $required
41		*
42		* @return array
43		*/
44	76	public function getNodeData($id, $required = false)
45		{
46	76	$query = $this->toBase();
47
48	76	$query->where($this->model->getKeyName(), '=', $id);
49
50	76	$data = $query->first([
51	76	$this->model->getLftName(),
52	76	$this->model->getRgtName(),
53	57	]);
54
55	76	if ( ! $data && $required) {
56	4	throw new ModelNotFoundException;
57		}
58
59	72	return (array) $data;
60		}
61
62		/**
63		* Get plain node data.
64		*
65		* @param mixed $id
66		* @param bool $required
67		*
68		* @return array
69		*/
70	48	public function getPlainNodeData($id, $required = false)
71		{
72	48	return array_values($this->getNodeData($id, $required));
73		}
74
75		/**
76		* Scope limits query to select just root node.
77		*
78		* @return self
79		*/
80	20	public function whereIsRoot()
81		{
82	20	$this->query->whereNull($this->model->getParentIdName());
83
84	20	return $this;
85		}
86
87		/**
88		* Limit results to ancestors of specified node.
89		*
90		* @param mixed $id
91		*
92		* @return self
93		*/
94	20	public function whereAncestorOf($id)
95		{
96	20	$keyName = $this->model->getKeyName();
97
98	20	if (NestedSet::isNode($id)) {
99	16	$value = '?';
100
101	16	$this->query->addBinding($id->getLft());
102
103	16	$id = $id->getKey();
104	12	} else {
105	4	$valueQuery = $this->model
106	4	->newQuery()
107	4	->toBase()
108	4	->select("_.".$this->model->getLftName())
109	4	->from($this->model->getTable().' as _')
110	4	->where($keyName, '=', $id)
111	4	->limit(1);
112
113	4	$this->query->mergeBindings($valueQuery);
114
115	4	$value = '(' . $valueQuery->toSql() . ')';
116		}
117
118	20	list($lft, $rgt) = $this->wrappedColumns();
119
120	20	$this->query->whereRaw("{$value} between {$lft} and {$rgt}");
121
122		// Exclude the node
123	20	$this->where($keyName, '<>', $id);
124
125	20	return $this;
126		}
127
128		/**
129		* Get ancestors of specified node.
130		*
131		* @param mixed $id
132		* @param array $columns
133		*
134		* @return self
135		*/
136	12	public function ancestorsOf($id, array $columns = ['*'])
137		{
138	12	return $this->whereAncestorOf($id)->get($columns);
139		}
140
141		/**
142		* Add node selection statement between specified range.
143		*
144		* @param array $values
145		* @param string $boolean
146		* @param bool $not
147		*
148		* @return self
149		*/
150	44	public function whereNodeBetween($values, $boolean = 'and', $not = false)
151		{
152	44	$this->query->whereBetween($this->model->getLftName(), $values, $boolean, $not);
153
154	44	return $this;
155		}
156
157		/**
158		* Add node selection statement between specified range joined with `or` operator.
159		*
160		* @param array $values
161		*
162		* @return self
163		*/
164		public function orWhereNodeBetween($values)
165		{
166		return $this->whereNodeBetween($values, 'or');
167		}
168
169		/**
170		* Add constraint statement to descendants of specified node.
171		*
172		* @param mixed $id
173		* @param string $boolean
174		* @param bool $not
175		*
176		* @return self
177		*/
178	48	public function whereDescendantOf($id, $boolean = 'and', $not = false)
179		{
180	48	$data = NestedSet::isNode($id)
181	47	? $id->getBounds()
182	48	: $this->model->newNestedSetQuery()->getPlainNodeData($id, true);
183
184		// Don't include the node
185	44	++$data[0];
186
187	44	return $this->whereNodeBetween($data, $boolean, $not);
188		}
189
190		/**
191		* @param mixed $id
192		*
193		* @return self
194		*/
195		public function whereNotDescendantOf($id)
196		{
197		return $this->whereDescendantOf($id, 'and', true);
198		}
199
200		/**
201		* @param mixed $id
202		*
203		* @return self
204		*/
205	4	public function orWhereDescendantOf($id)
206		{
207	4	return $this->whereDescendantOf($id, 'or');
208		}
209
210		/**
211		* @param mixed $id
212		*
213		* @return self
214		*/
215		public function orWhereNotDescendantOf($id)
216		{
217		return $this->whereDescendantOf($id, 'or', true);
218		}
219
220		/**
221		* Get descendants of specified node.
222		*
223		* @param mixed $id
224		* @param array $columns
225		*
226		* @return \Arcanedev\LaravelNestedSet\Eloquent\Collection
227		*/
228		public function descendantsOf($id, array $columns = ['*'])
229		{
230		try {
231		return $this->whereDescendantOf($id)->get($columns);
232		}
233		catch (ModelNotFoundException $e) {
234		return $this->model->newCollection();
235		}
236		}
237
238		/**
239		* @param mixed $id
240		* @param string $operator
241		* @param string $boolean
242		*
243		* @return self
244		*/
245		protected function whereIsBeforeOrAfter($id, $operator, $boolean)
246		{
247		if (NestedSet::isNode($id)) {
248		$value = '?';
249
250		$this->query->addBinding($id->getLft());
251		} else {
252		$valueQuery = $this->model
253		->newQuery()
254		->toBase()
255		->select('_n.'.$this->model->getLftName())
256		->from($this->model->getTable().' as _n')
257		->where('_n.'.$this->model->getKeyName(), '=', $id);
258
259		$this->query->mergeBindings($valueQuery);
260
261		$value = '('.$valueQuery->toSql().')';
262		}
263
264		list($lft,) = $this->wrappedColumns();
265
266		$this->query->whereRaw("{$lft} {$operator} {$value}", [ ], $boolean);
267
268		return $this;
269		}
270
271		/**
272		* Constraint nodes to those that are after specified node.
273		*
274		* @param mixed $id
275		* @param string $boolean
276		*
277		* @return self
278		*/
279		public function whereIsAfter($id, $boolean = 'and')
280		{
281		return $this->whereIsBeforeOrAfter($id, '>', $boolean);
282		}
283
284		/**
285		* Constraint nodes to those that are before specified node.
286		*
287		* @param mixed $id
288		* @param string $boolean
289		*
290		* @return self
291		*/
292		public function whereIsBefore($id, $boolean = 'and')
293		{
294		return $this->whereIsBeforeOrAfter($id, '<', $boolean);
295		}
296
297		/**
298		* Include depth level into the result.
299		*
300		* @param string $as
301		*
302		* @return self
303		*/
304	16	public function withDepth($as = 'depth')
305		{
306	16	if ($this->query->columns === null) {
307	16	$this->query->columns = ['*'];
308	12	}
309
310	16	$table = $this->wrappedTable();
311
312	16	list($lft, $rgt) = $this->wrappedColumns();
313
314	16	$query = $this->model
315	16	->newScopedQuery('_d')
316	16	->toBase()
317	16	->selectRaw('count(1) - 1')
318	16	->from($this->model->getTable().' as _d')
319	16	->whereRaw("{$table}.{$lft} between _d.{$lft} and _d.{$rgt}");
320
321	16	$this->query->selectSub($query, $as);
322
323	16	return $this;
324		}
325
326		/**
327		* Get wrapped `lft` and `rgt` column names.
328		*
329		* @return array
330		*/
331	48	protected function wrappedColumns()
332		{
333	48	$grammar = $this->query->getGrammar();
334
335		return [
336	48	$grammar->wrap($this->model->getLftName()),
337	48	$grammar->wrap($this->model->getRgtName()),
338	36	];
339		}
340
341		/**
342		* Get a wrapped table name.
343		*
344		* @return string
345		*/
346	28	protected function wrappedTable()
347		{
348	28	return $this->query->getGrammar()->wrapTable($this->getQuery()->from);
349		}
350
351		/**
352		* Wrap model's key name.
353		*
354		* @return string
355		*/
356	12	protected function wrappedKey()
357		{
358	12	return $this->query->getGrammar()->wrap($this->model->getKeyName());
359		}
360
361		/**
362		* Exclude root node from the result.
363		*
364		* @return self
365		*/
366	8	public function withoutRoot()
367		{
368	8	$this->query->whereNotNull($this->model->getParentIdName());
369
370	8	return $this;
371		}
372
373		/**
374		* Order by node position.
375		*
376		* @param string $dir
377		*
378		* @return self
379		*/
380	52	public function defaultOrder($dir = 'asc')
381		{
382	52	$this->query->orders = [];
383	52	$this->query->orderBy($this->model->getLftName(), $dir);
384
385	52	return $this;
386		}
387
388		/**
389		* Order by reversed node position.
390		*
391		* @return self
392		*/
393	4	public function reversed()
394		{
395	4	return $this->defaultOrder('desc');
396		}
397
398		/**
399		* Move a node to the new position.
400		*
401		* @param mixed $key
402		* @param int $position
403		*
404		* @return int
405		*/
406	40	public function moveNode($key, $position)
407		{
408	40	list($lft, $rgt) = $this->model->newNestedSetQuery()
409	40	->getPlainNodeData($key, true);
410
411	40	if ($lft < $position && $position <= $rgt) {
412		throw new LogicException('Cannot move node into itself.');
413		}
414
415		// Get boundaries of nodes that should be moved to new position
416	40	$from = min($lft, $position);
417	40	$to = max($rgt, $position - 1);
418
419		// The height of node that is being moved
420	40	$height = $rgt - $lft + 1;
421
422		// The distance that our node will travel to reach it's destination
423	40	$distance = $to - $from + 1 - $height;
424
425		// If no distance to travel, just return
426	40	if ($distance === 0) {
427		return 0;
428		}
429
430	40	if ($position > $lft) {
431	32	$height *= -1;
432	24	} else {
433	8	$distance *= -1;
434		}
435
436	40	$boundary = [$from, $to];
437		$query = $this->toBase()->where(function (Query $inner) use ($boundary) {
438	40	$inner->whereBetween($this->model->getLftName(), $boundary);
439	40	$inner->orWhereBetween($this->model->getRgtName(), $boundary);
440	40	});
441
442	40	return $query->update($this->patch(
443	40	compact('lft', 'rgt', 'from', 'to', 'height', 'distance')
444	30	));
445		}
446
447		/**
448		* Make or remove gap in the tree. Negative height will remove gap.
449		*
450		* @param int $cut
451		* @param int $height
452		*
453		* @return int
454		*/
455	48	public function makeGap($cut, $height)
456		{
457		$query = $this->toBase()->whereNested(function (Query $inner) use ($cut) {
458	48	$inner->where($this->model->getLftName(), '>=', $cut);
459	48	$inner->orWhere($this->model->getRgtName(), '>=', $cut);
460	48	});
461
462	48	return $query->update($this->patch(
463	48	compact('cut', 'height')
464	36	));
465		}
466
467		/**
468		* Get patch for columns.
469		*
470		* @param array $params
471		*
472		* @return array
473		*/
474	84	protected function patch(array $params)
475		{
476	84	$grammar = $this->query->getGrammar();
477	84	$columns = [];
478
479	84	foreach ([$this->model->getLftName(), $this->model->getRgtName()] as $col) {
480	84	$columns[$col] = $this->columnPatch($grammar->wrap($col), $params);
481	63	}
482
483	84	return $columns;
484		}
485
486		/**
487		* Get patch for single column.
488		*
489		* @param string $col
490		* @param array $params
491		*
492		* @return string
493		*/
494	84	protected function columnPatch($col, array $params)
495		{
496		/**
497		* @var int $height
498		* @var int $distance
499		* @var int $lft
500		* @var int $rgt
501		* @var int $from
502		* @var int $to
503		*/
504	84	extract($params);
505
506	84	if ($height > 0) $height = '+'.$height;
507
508	84	if (isset($cut)) {
509	48	return new Expression("case when {$col} >= {$cut} then {$col}{$height} else {$col} end");
510		}
511
512	40	if ($distance > 0) {
513	32	$distance = '+'.$distance;
514	24	}
515
516	40	return new Expression(
517		"case ".
518	40	"when {$col} between {$lft} and {$rgt} then {$col}{$distance} ". // Move the node
519	40	"when {$col} between {$from} and {$to} then {$col}{$height} ". // Move other nodes
520	40	"else {$col} end"
521	30	);
522		}
523
524		/**
525		* Get statistics of errors of the tree.
526		*
527		* @return array
528		*/
529	12	public function countErrors()
530		{
531		$checks = [
532	12	'oddness' => $this->getOddnessQuery(), // Check if lft and rgt values are ok
533	12	'duplicates' => $this->getDuplicatesQuery(), // Check if lft and rgt values are unique
534	12	'wrong_parent' => $this->getWrongParentQuery(), // Check if parent_id is set correctly
535	12	'missing_parent' => $this->getMissingParentQuery() // Check for nodes that have missing parent
536	9	];
537
538	12	$query = $this->query->newQuery();
539
540	12	foreach ($checks as $key => $inner) {
541		/** @var \Illuminate\Database\Query\Builder $inner */
542	12	$inner->selectRaw('count(1)');
543
544	12	$query->selectSub($inner, $key);
545	9	}
546
547	12	return (array) $query->first();
548		}
549
550		/**
551		* Get the oddness errors query.
552		*
553		* @return \Illuminate\Database\Query\Builder
554		*/
555	12	protected function getOddnessQuery()
556		{
557	12	return $this->model
558	12	->newNestedSetQuery()
559	12	->toBase()
560		->whereNested(function (Query $inner) {
561	12	list($lft, $rgt) = $this->wrappedColumns();
562
563	12	$inner->whereRaw("{$lft} >= {$rgt}")
564	12	->orWhereRaw("({$rgt} - {$lft}) % 2 = 0");
565	12	});
566		}
567
568		/**
569		* Get the duplicates errors query.
570		*
571		* @return \Arcanedev\LaravelNestedSet\Eloquent\QueryBuilder\|\Illuminate\Database\Query\Builder
572		*/
573	12	protected function getDuplicatesQuery()
574		{
575	12	$table = $this->wrappedTable();
576
577	12	$query = $this->model
578	12	->newNestedSetQuery('c1')
579	12	->toBase()
580	12	->from($this->query->raw("{$table} c1, {$table} c2"))
581	12	->whereRaw("c1.id < c2.id")
582		->whereNested(function (Query $inner) {
583	12	list($lft, $rgt) = $this->wrappedColumns();
584
585	12	$inner->orWhereRaw("c1.{$lft}=c2.{$lft}")
586	12	->orWhereRaw("c1.{$rgt}=c2.{$rgt}")
587	12	->orWhereRaw("c1.{$lft}=c2.{$rgt}")
588	12	->orWhereRaw("c1.{$rgt}=c2.{$lft}");
589	12	});
590
591	12	return $this->model->applyNestedSetScope($query, 'c2');
592		}
593
594		/**
595		* Get the wrong parent query.
596		*
597		* @return \Illuminate\Database\Query\Builder
598		*/
599	12	protected function getWrongParentQuery()
600		{
601	12	$table = $this->wrappedTable();
602	12	$keyName = $this->wrappedKey();
603	12	$parentIdName = $this->query->raw($this->model->getParentIdName());
604	12	$query = $this->model->newNestedSetQuery('c')
605	12	->toBase()
606	12	->from($this->query->raw("{$table} c, {$table} p, $table m"))
607	12	->whereRaw("c.{$parentIdName}=p.{$keyName}")
608	12	->whereRaw("m.{$keyName} <> p.{$keyName}")
609	12	->whereRaw("m.{$keyName} <> c.{$keyName}")
610		->whereNested(function (Query $inner) {
611	12	list($lft, $rgt) = $this->wrappedColumns();
612
613	12	$inner->whereRaw("c.{$lft} not between p.{$lft} and p.{$rgt}")
614	12	->orWhereRaw("c.{$lft} between m.{$lft} and m.{$rgt}")
615	12	->whereRaw("m.{$lft} between p.{$lft} and p.{$rgt}");
616	12	});
617
618	12	$this->model->applyNestedSetScope($query, 'p');
619	12	$this->model->applyNestedSetScope($query, 'm');
620
621	12	return $query;
622		}
623
624		/**
625		* Get the missing parent query.
626		*
627		* @return \Illuminate\Database\Query\Builder
628		*/
629	12	protected function getMissingParentQuery()
630		{
631	12	return $this->model
632	12	->newNestedSetQuery()
633	12	->toBase()
634	12	->whereNested(function (Query $inner) {
635	12	$table = $this->wrappedTable();
636	12	$keyName = $this->wrappedKey();
637	12	$parentIdName = $this->query->raw($this->model->getParentIdName());
638
639	12	$query = $this->model
640	12	->newNestedSetQuery()
641	12	->toBase()
642	12	->selectRaw('1')
643	12	->from($this->query->raw("{$table} p"))
644	12	->whereRaw("{$table}.{$parentIdName} = p.{$keyName}")
645	12	->limit(1);
646
647	12	$this->model->applyNestedSetScope($query, 'p');
648
649	12	$inner->whereRaw("{$parentIdName} is not null")
650	12	->addWhereExistsQuery($query, 'and', true);
651	12	});
652		}
653
654		/**
655		* Get the number of total errors of the tree.
656		*
657		* @return int
658		*/
659	8	public function getTotalErrors()
660		{
661	8	return array_sum($this->countErrors());
662		}
663
664		/**
665		* Get whether the tree is broken.
666		*
667		* @return bool
668		*/
669	8	public function isBroken()
670		{
671	8	return $this->getTotalErrors() > 0;
672		}
673
674		/**
675		* Fixes the tree based on parentage info.
676		* Nodes with invalid parent are saved as roots.
677		*
678		* @return int The number of fixed nodes
679		*/
680	4	public function fixTree()
681		{
682	4	$dictionary = $this->defaultOrder()
683	4	->get([
684	4	$this->model->getKeyName(),
685	4	$this->model->getParentIdName(),
686	4	$this->model->getLftName(),
687	4	$this->model->getRgtName(),
688	3	])
689	4	->groupBy($this->model->getParentIdName())
690	4	->all();
691
692	4	return TreeHelper::fixNodes($dictionary);
693		}
694
695		/**
696		* Rebuild the tree based on raw data.
697		* If item data does not contain primary key, new node will be created.
698		*
699		* @param array $data
700		* @param bool $delete Whether to delete nodes that exists but not in the data array
701		*
702		* @return int
703		*/
704	12	public function rebuildTree(array $data, $delete = false)
705		{
706	12	$existing = $this->get()->getDictionary();
707
708	12	return TreeHelper::rebuild($data, $existing, $this->model, $delete);
709		}
710
711		/**
712		* Get the root node.
713		*
714		* @param array $columns
715		*
716		* @return \Illuminate\Database\Eloquent\Model\|\Arcanedev\LaravelNestedSet\Contracts\Nodeable\|null
717		*/
718	16	public function root(array $columns = ['*'])
719		{
720	16	return $this->whereIsRoot()->first($columns);
721		}
722		}
723

ARCANEDEV / LaravelNestedSet

Pull Request — master (#1)

QueryBuilder::rebuildTree() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like