Support ordering by more columns?

[Derokorian]

So my brian is total poop today, and I need help figuring out the solution to my problem. I have the below code, with a nice little todo that I'd like to turn into a done.

// ToDo: Better parsing of order_by, currently only supports ordering by a single column
if (isset($aOpts['order_by']) &&
    ((strpos($aOpts['order_by'], ' ') !== FALSE &&
      isset(static::$COLUMNS[substr($aOpts['order_by'], 0, strpos($aOpts['order_by'], ' '))])) ||
    isset(static::$COLUMNS[$aOpts['order_by']]))) {
    $sql .= 'ORDER BY ' . $strColPrefix . $aOpts['order_by'] . ' ';
}

What this does is let me pass in an option $aOpts['order_by'] and set it equal to something like 'modified desc' or 'user_id'. This checks if the order_by is set, and if its a is a valid column name for the given table (as defined in static::$COLUMNS). If so, it adds the prefix for the current query (table alias) so that we get something like:

...SQL... ORDER BY p.modified desc
...SQL... ORDER BY u.user_id

What I'm looking for is the best way to support ordering on multiple columns. I'm thinking there are basically 2 options, but I'm not sure what's best choice. In either case, I need to add validation that the direction is valid. Currently I'm only checking the column name is valid, and this is a problem.

Option A: order_by may be an array, containing strings of the same type mentioned above in the order preferred.

$aOpts['order_by'] = 'modified desc'; // ORDER BY p.modified DESC -- case changed when using fixed strings for direction
$aOpts['order_by'] = [
    'modified desc',
    'published',
    'user_id asc'
]; // ORDER BY p.modified DESC, p.published ASC, p.user_id ASC

if (isset($aOpts['order_by'])) { 
    $sOrderBy = ''; 
    if (!is_array($aOpts['order_by'])) { 
        $aOpts['order_by'] = [($aOpts['order_by']]; 
    } 
    foreach ($aOpts['order_by'] as $sOrder) { 
        list($sColumn, $sDirection) = explode(' ', $sOrder, 2); 
        $sDirection = strtoupper($sDirection) ?: 'ASC';
        if (/* is valid column and direction */true) { 
                strlen($sOrderBy) == 0 ?: $sOrderBy .= ', ';
            $sOrderBy .= "$sColumn $sDirection"; 
        } 
    } 
    strlen($sOrderBy) == 0 ?: $sql .= "ORDER BY $sOrderBy "; 
}  

Option B: order_by must be an array of arrays of the form 'col'=>'name','dir'=>'asc|desc'. String only is not accepted any more (requires more refactoring, easier to validate though).

$aOpts['order_by'] = 'modified desc'; // fails, not an array
$aOpts['order_by'] = ['modified desc']; // fails, not a valid array
$aOpts['order_by'] = [
    ['col' => 'modified', 'dir' => 'desc'],
    ['col' => 'published', 'dir' => 'asc'],
    ['col' => 'user_id', 'dir' => 'asc']
]; // ORDER BY p.modified DESC, p.published ASC, p.user_id ASC

if (isset($aOpts['order_by'])) {
    $sOrderBy = '';
    if (is_array($aOpts['order_by'])) {
        foreach ($aOpts['order_by'] as $aOrder) {
            $aOrder['dir'] = strtoupper($aOrder['dir']);
            if (/* is valid column and direction */ true) {
                strlen($sOrderBy) == 0 ?: $sOrderBy .= ', ';
                $sOrderBy .= "$aOrder[col] $aOrder[dir]";
            }
        }
    }
    strlen($sOrderBy) == 0 ?: $sql .= "ORDER BY $sOrderBy ";
}

[NogDog]

I'm sure there are spiffier ways to do this, but this seems pretty flexible:

<?php

$orderInputs = array(
    array('col1'),
    array('col2', 'desc'),
    array('col3', 'asc'),
    'col4'
);

$orderBy = array();
foreach($orderInputs as $input) {
    if(is_array($input)) {
        $orderBy[] = implode(' ', $input);
    }
    else {
        $orderBy[] = $input;
    }
}
$orderByClause = '';
if(!empty($orderBy)) {
    $orderByClause = 'ORDER BY '.implode(', ', $orderBy);
}
echo $orderByClause.PHP_EOL;

[Weedpacket]

Since column names can't begin with a hyphen then, by analogy with the positive/negative bimodality of integers, [font=monospace]["col1", "-col2", "col3"][/font] could indicate "col1 asc, col2 desc, col3 asc". Maybe for completeness, allow [font=monospace]"+col"[/font] to explicitly indicate ascending sort. "String only" input would be a space/comma-separated list of those. If [font=monospace]+[/font] were required, then separators wouldn't be needed at all.

It's basically Option A, with validation built into the parsing: look at the first character of the element. If it's not [font=monospace]+[/font] or [font=monospace]-[/font] then the element is the name of the column and the direction is ascending.

Go with Option B and for a robust consistency across the SQL-building process you'll be heading toward building a SQL statement generator to handle all the different syntactic structures.

But don't mind me too much: right now my brain is off-balance in my skull and my eyeballs are miked.