ZF-7619: Zend_Db_Adapter_Pdo_Mssql, funtion Limit()

Description

Zend_Db_Adapter_Pdo_Mssql, funtion Limit()

1) this function required Order value for working, but no Exception if not set

2) when uses $select->limit($count, $offset) and $offset > count($records), this function return $count last records, but must NULL

below my function with corrections:


public function limit($sql, $count, $offset = 0)
     {
        $count = intval($count);
        if ($count <= 0) {
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
        }

        $orderby = stristr($sql, 'ORDER BY');
        if ($orderby === false) {
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("ORDER value require");
        }
        $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
        $order = str_ireplace('ORDER BY', '', $orderby);
        $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
        
        $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count+$offset) . ' ROW_NUMBER() OVER(Order BY '.$order.' '.$sort.') as RowNumber, ', $sql);

        $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl ' . ' WHERE RowNumber between '.($offset+1).' and '.($count+$offset);
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ';
            $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
        }
        $sql .= ') AS outer_tbl';
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ' . $sort;
        }

        return $sql;
    }

Comments

Set component and auto reassign

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.