[Solar-talk] query expressions

[Rodrigo Moraes]

Hi,
At some point I needed an archive list by months from my nodes table
and then I created this query:

    $select = Solar::factory('Solar_Sql_Select');
    // @todo Support for query expressions (substring).
    $select->from($this->_nodes_table, array(
        'SUBSTRING(created,1,4) AS year',
        'SUBSTRING(created,6,2) AS month',
        'SUBSTRING(created,9,2) AS day',
    ));
    $select->group(array('year', 'month'));

SUBSTRING() will work on MySQL. In Sqlite it is substr() and in
PostgreSql it is Substring() but with a different parameters syntax.
Then I planned a simple way to support query expressions in Solar_Sql.
The example above would become:

    // build the expressions using Solar_Sql
    $substr = $solar_sql->expression('substring', array('created', 1, 4));
    $substr2 = $solar_sql->expression('substring', array('created', 6, 2));
    $substr3 = $solar_sql->expression('substring', array('created', 9, 2));

    $select->from($this->_nodes_table, array(
        $substr . ' AS year',
        $substr2 . ' AS month',
        $substr3 . ' AS day',
    ));

this would be the Solar_Sql method:

    public function expression($type, $params = array()) {
        return $this->_adapter->expression($type, $params);
    }

Each adapter would then call a Expressions class (MysqlExpressions,
SqliteExpressions etc) and return a expression of type $type.

It is easy to start with support for substring() in each database
engine. Later other expressions types can be added.

What do you think? Wouldn't this be useful?

rodrigo

---------------------------------------------------------------------
Solar has now a Community Wiki: solarphp.org
---------------------------------------------------------------------
Join the #solarphp IRC channel on freenode.org
---------------------------------------------------------------------