shjchen

5143 经验值

原先的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;
    }

求修改后的代码: