SilverStripe\MSSQL\MSSQLQueryBuilder::buildSelectQuery()

Complexity

Conditions	8
Paths	20

Size

Total Lines	59
Code Lines	32

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
    protected function buildSelectQuery(SQLSelect $query, array &$parameters)
    {
        list($limit, $offset) = $this->parseLimit($query);
        
        // If not using ofset then query generation is quite straightforward
        if (empty($offset)) {
            $sql = parent::buildSelectQuery($query, $parameters);
            // Inject limit into SELECT fragment
            if (!empty($limit)) {
                $sql = preg_replace('/^(SELECT (DISTINCT)?)/i', '${1} TOP '.$limit, $sql);
            }
            return $sql;
        }
        
        // When using offset we must use a subselect
        // @see http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server
        $orderby = $query->getOrderBy();

        // workaround for subselect not working with alias functions
        // just use the function directly in the order by instead of the alias
        $selects = $query->getSelect();
        foreach ($orderby as $field => $dir) {
            if (preg_match('/_SortColumn/', $field)) {
                unset($orderby[$field]);
                $orderby[$selects[str_replace('"', '', $field)]] = $dir;
            }
        }

        // Create order expression, using the first column if none explicitly specified
        if ($orderby) {

The expression $orderby of type array is implicitly converted to a boolean; are you sure this is intended? If so, consider using ! empty($expr) instead to make it clear that you intend to check for an array without elements.

            // Simple implementation of buildOrderByFragment
            $statements = array();
            foreach ($orderby as $clause => $dir) {
                $statements[] = trim("$clause $dir");
            }
            $orderByClause = "ORDER BY " . implode(', ', $statements);
        } else {
            $selects = $query->getSelect();
            $firstCol = reset($selects);
            $orderByClause = "ORDER BY $firstCol";
        }
        
        // Build main query SQL
        $sql = parent::buildSelectQuery($query, $parameters);
        
        // Inject row number into selection
        $sql = preg_replace('/^(SELECT (DISTINCT)?)/i', '${1} ROW_NUMBER() OVER ('.$orderByClause.') AS Number, ', $sql);
        
        // Sub-query this SQL
        if (empty($limit)) {
            $limitCondition = "Number > ?";
            $parameters[] = $offset;
        } else {
            $limitCondition = "Number BETWEEN ? AND ?";
            $parameters[] = $offset + 1;
            $parameters[] = $offset + $limit;
        }
        return "SELECT * FROM ($sql) AS Numbered WHERE $limitCondition ORDER BY Number";
    }