QueryBuilder::whereAncestorOf() - Code Metrics - ARCANEDEV/LaravelNestedSet - Measure and Improve Code Quality continuously with Scrutinizer

QueryBuilder::whereAncestorOf() B
last analyzed 2017-04-15 17:12 UTC

↳ Parent: QueryBuilder

Complexity

Conditions	2
Paths	2

Size

Total Lines	33
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	20
CRAP Score	2

Importance

Changes

Metric	Value
dl	0
loc	33
c	0
b	0
f	0
ccs	20
cts	20
cp	1
rs	8.8571
cc	2
eloc	20
nc	2
nop	1
crap	2

<?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 Methods
     | -----------------------------------------------------------------
     */
    /**
     * 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|\Illuminate\Database\Eloquent\Collection
     */
    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 or self.
     *
     * @param  mixed   $id
     * @param  string  $boolean
     * @param  bool    $not
     *
     * @return self
     */
    public function whereDescendantOrSelf($id, $boolean = 'and', $not = false)
    {
        return $this->whereDescendantOf($id, $boolean, $not, true);
    }

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

        if ( ! $andSelf) ++$data[0];

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

    /**
     * Get descendants of specified node.
     *
     * @param  mixed  $id
     * @param  array  $columns
     * @param  bool   $andSelf
     *
     * @return \Arcanedev\LaravelNestedSet\Eloquent\Collection|\Illuminate\Database\Eloquent\Collection
     */
    public function descendantsOf($id, array $columns = ['*'], $andSelf = false)
    {
        try {
            return $this->whereDescendantOf($id, 'and', false, $andSelf)->get($columns);

        }
        catch (ModelNotFoundException $e) {
            return $this->model->newCollection();
        }
    }

    /**
     * Get descendants of self node.
     *
     * @param  mixed  $id
     * @param  array  $columns
     *
     * @return \Arcanedev\LaravelNestedSet\Eloquent\Collection
     */
    public function descendantsAndSelf($id, array $columns = ['*'])
    {
        return $this->descendantsOf($id, $columns, true);
    }

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

ARCANEDEV / LaravelNestedSet

QueryBuilder::whereAncestorOf() B last analyzed 2017-04-15 17:12 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

QueryBuilder::whereAncestorOf() B
last analyzed 2017-04-15 17:12 UTC