Using case/when/then in SQL queries of Zend Framework 2

Question

I have query :

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 using ZF2 and Zend\..\Sql, I can implement this query ?

Update:

Thank who try to help me. It is work as following:

$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 better way to solve your problem with parameterized. Your updated answer solved my issue but then I made 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 ';

And below is rest of your code. Just look at $case variable which I have generated in above code. Why $case is string? Because Zend_Db_Expr requires String value.

$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');