原先的sql
SELECT *, (SELECT COUNT(1) - 1 FROM el_category
AS _d
WHERE el_category
._lft
BETWEEN _d
._lft
AND _d
._rgt
) AS depth
FROM el_category
## 需要修改成的sql
SELECT * FROM (
SELECT *, (SELECT COUNT(1) - 1 FROM el_category
AS _d
WHERE el_category
._lft
BETWEEN _d
._lft
AND _d
._rgt
) AS depth
FROM el_category
) AS temp
WHERE depth
<= 2;
原先的代码:
/**
* test
*
*/
public static function test()
{
return $result = self::withDepth()->get();
}
/**
* Include depth level into the result.
*
* @param string $as
*
* @return $this
*/
public function withDepth($as = 'depth')
{
if ($this->query->columns === null) $this->query->columns = [ '*' ];
$table = $this->wrappedTable();
list($lft, $rgt) = $this->wrappedColumns();
$alias = '_d';
$wrappedAlias = $this->query->getGrammar()->wrapTable($alias);
$query = $this->model
->newScopedQuery('_d')
->toBase()
->selectRaw('count(1) - 1')
->from($this->model->getTable().' as '.$alias)
->whereRaw("{$table}.{$lft} between {$wrappedAlias}.{$lft} and {$wrappedAlias}.{$rgt}");
$this->query->selectSub($query, $as);
return $this->query;
}
求修改后的代码: