QueryBuilder::ancestorsOf() - 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-09 13:48 UTC

QueryBuilder::ancestorsOf() A

↳ Parent: QueryBuilder

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	4
ccs	2
cts	2
cp	1
rs	10
cc	1
eloc	2
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');
    }

    /**
     * @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);
    }

    /**
     * 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);

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

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

    /**
     * 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);

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

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

ARCANEDEV / LaravelNestedSet

Pull Request — master (#1)

QueryBuilder::ancestorsOf() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like