Using case / when / then in Zend Framework 2 SQL Queries

Question

I have a request:

SELECT user_name, group_id,
       CASE WHEN col_1 = 1 THEN 0 
            WHEN col_2 = 1 THEN 1 
            WHEN col_3 = 1 THEN 2 
       END as merge_col
FROM some_table
WHERE group_id = 10
ORDER BY merge_col.

How to use ZF2 and Zend \ .. \ Sql , can I implement this query?

Update:

Thanks for trying to help me. This works as follows:

$select->columns(array(
            'user_name', 
            'group_id', 
            'merge_col' => new Expression('CASE WHEN col_1 = 1 THEN 0 
                     WHEN col_2 = 1 THEN 1 
                     WHEN col_3 = 1 THEN 2 END')))
->where (array('group_id'=> 10))
->order ('merge_col');

is there a better answer?

Thanks.

Answer 1

$select = $sql->select ('some_table');
$select->columns(array(
            new Expression('CASE WHEN col_1 = 1 THEN 0 
                     WHEN col_2 = 1 THEN 1 
                     WHEN col_3 = 1 THEN 2 
                         END AS merge_col'), 'user_name', 'group_id'))
->where ('group_id = 10')
->order ('merge_col');

Answer 2

Here is one of the best ways to solve your parameterization problem. Your updated answer solved my problem, but then I did it better with parameterized.

$case = 'CASE ';
$sqlCase = $this->getAdapter();
$case .= $sqlCase->quoteInto('WHEN col_1 = 1 THEN ? ', yourVariable, Zend_Db::INT_TYPE);
$case .= $sqlCase->quoteInto('WHEN col_2 = 1 THEN ? ', yourVariable, Zend_Db::INT_TYPE);
$case .= $sqlCase->quoteInto('WHEN col_2 = 1 THEN ? ', yourVariable, Zend_Db::INT_TYPE);
$case .= 'ELSE 0 END ';

- . $case, . $case - ? Zend_Db_Expr String.

$select =$this->getAdapter();
$select->columns(array(
            'user_name', 
            'group_id', 
            'merge_col' => new Zend_Db_Expr($case)))
       ->where (array('group_id'=> 10))
       ->order ('merge_col');

Answer 3

Use the expression Zend \ Db \ Sql \ Expression:

    use Zend\Db\Sql;
    ....

    $sql = $this->tableGateway->getSql();
    $select = $sql->select();
    $select->columns(array('user_name', 'group_id',
            new Sql\Expression('CASE WHEN col_1 = 1 THEN 0 
                     WHEN col_2 = 1 THEN 1 
                     WHEN col_3 = 1 THEN 2 
                         END as merge_col')));

    return $this->tableGateway->selectWith($select);