protected function _pagingSubquery($original, $limit, $offset)
    {
        $field = '_cake_paging_._cake_page_rownum_';

        if ($original->clause('order')) {
            // SQL server does not support column aliases in OVER clauses.  But
            // the only practical way to specify the use of calculated columns
            // is with their alias.  So substitute the select SQL in place of
            // any column aliases for those entries in the order clause.
            $select = $original->clause('select');
            $order = new OrderByExpression();
            $original
                ->clause('order')
                ->iterateParts(function ($direction, $orderBy) use ($select, $order) {
                    $key = $orderBy;
                    if (isset($select[$orderBy]) &&
                        $select[$orderBy] instanceof ExpressionInterface
                    ) {
                        $key = $select[$orderBy]->sql(new ValueBinder());
                    }
                    $order->add([$key => $direction]);

                    // Leave original order clause unchanged.
                    return $orderBy;
                });
        } else {
            $order = new OrderByExpression('(SELECT NULL)');
        }

        $query = clone $original;
        $query->select([
                '_cake_page_rownum_' => new UnaryExpression('ROW_NUMBER() OVER', $order)
            ])->limit(null)
            ->offset(null)
            ->order([], true);

        $outer = new Query($query->getConnection());
        $outer->select('*')
            ->from(['_cake_paging_' => $query]);

        if ($offset) {
            $outer->where(["$field > " . (int)$offset]);
        }
        if ($limit) {
            $value = (int)$offset + (int)$limit;
            $outer->where(["$field <= $value"]);
        }

        // Decorate the original query as that is what the
        // end developer will be calling execute() on originally.
        $original->decorateResults(function ($row) {
            if (isset($row['_cake_page_rownum_'])) {
                unset($row['_cake_page_rownum_']);
            }

            return $row;
        });

        return $outer;
    }
protected function _pagingSubquery($original, $limit, $offset)
    {
        $field = '_cake_paging_._cake_page_rownum_';

        if ($original->clause('order')) {
            // SQL server does not support column aliases in OVER clauses.  But
            // the only practical way to specify the use of calculated columns
            // is with their alias.  So substitute the select SQL in place of
            // any column aliases for those entries in the order clause.
            $select = $original->clause('select');
            $order = new OrderByExpression();
            $original
                ->clause('order')
                ->iterateParts(function ($direction, $orderBy) use ($select, $order) {
                    $key = $orderBy;
                    if (isset($select[$orderBy]) &&
                        $select[$orderBy] instanceof ExpressionInterface
                    ) {
                        $key = $select[$orderBy]->sql(new ValueBinder());
                    }
                    $order->add([$key => $direction]);

                    // Leave original order clause unchanged.
                    return $orderBy;
                });
        } else {
            $order = new OrderByExpression('(SELECT NULL)');
        }

        $query = clone $original;
        $query->select([
                '_cake_page_rownum_' => new UnaryExpression('ROW_NUMBER() OVER', $order)
            ])->limit(null)
            ->offset(null)
            ->order([], true);

        $outer = new Query($query->getConnection());
        $outer->select('*')
            ->from(['_cake_paging_' => $query]);

        if ($offset) {
            $outer->where(["$field > " . (int)$offset]);
        }
        if ($limit) {
            $value = (int)$offset + (int)$limit;
            $outer->where(["$field <= $value"]);
        }

        // Decorate the original query as that is what the
        // end developer will be calling execute() on originally.
        $original->decorateResults(function ($row) {
            if (isset($row['_cake_page_rownum_'])) {
                unset($row['_cake_page_rownum_']);
            }

            return $row;
        });

        return $outer;
    }
protected function _pagingSubquery ( $ original , $ limit , $ offset ) { $ field = '_cake_paging_._cake_page_rownum_' ; if ( $ original -> clause ( 'order' ) ) { // SQL server does not support column aliases in OVER clauses.  But // the only practical way to specify the use of calculated columns // is with their alias.  So substitute the select SQL in place of // any column aliases for those entries in the order clause. $ select = $ original -> clause ( 'select' ) ; $ order = new OrderByExpression ( ) ; $ original -> clause ( 'order' ) -> iterateParts ( function ( $ direction , $ orderBy ) use ( $ select , $ order ) { $ key = $ orderBy ; if ( isset ( $ select [ $ orderBy ] ) && $ select [ $ orderBy ] instanceof ExpressionInterface ) { $ key = $ select [ $ orderBy ] -> sql ( new ValueBinder ( ) ) ; } $ order -> add ( [ $ key => $ direction ] ) ; // Leave original order clause unchanged. return $ orderBy ; } ) ; } else { $ order = new OrderByExpression ( '(SELECT NULL)' ) ; } $ query = clone $ original ; $ query -> select ( [ '_cake_page_rownum_' => new UnaryExpression ( 'ROW_NUMBER() OVER' , $ order ) ] ) -> limit ( null ) -> offset ( null ) -> order ( [ ] , true ) ; $ outer = new Query ( $ query -> getConnection ( ) ) ; $ outer -> select ( '*' ) -> from ( [ '_cake_paging_' => $ query ] ) ; if ( $ offset ) { $ outer -> where ( [ "$field > " . ( int ) $ offset ] ) ; } if ( $ limit ) { $ value = ( int ) $ offset + ( int ) $ limit ; $ outer -> where ( [ "$field <= $value" ] ) ; } // Decorate the original query as that is what the // end developer will be calling execute() on originally. $ original -> decorateResults ( function ( $ row ) { if ( isset ( $ row [ '_cake_page_rownum_' ] ) ) { unset ( $ row [ '_cake_page_rownum_' ] ) ; } return $ row ; } ) ; return $ outer ; }
Generate a paging subquery for older versions of SQLserver.

Prior to SQLServer 2012 there was no equivalent to LIMIT OFFSET, so a subquery must
be used.

@param \Cake\Database\Query $original The query to wrap in a subquery.
@param int $limit The number of rows to fetch.
@param int $offset The number of rows to offset.
@return \Cake\Database\Query Modified query object.
Generate a paging subquery for older versions of SQLserver .