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.

You could also use `?` instead of numbers in the Expression object so ZF2 can scape it: `new Expression('CASE WHEN col_1 = ? THEN ? WHEN col_2 = ? THEN ? WHEN col_3 = ? THEN ? END', array(1, 0, 1, 1, 1, 2))` — Edson Horacio Junior, Apr 04 '14 at 14:30

score 5 · Answer 1 · answered Nov 26 '13 at 19:09

5

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

answered Nov 26 '13 at 19:09

akond

15,865
4
35
55

Unfortunately it doesn't work and I receive **Zend\Db\Adapter\Exception\InvalidQueryException**. However base your answer I have found right solution. See question update. Thanks. – Maksym Kalin Nov 27 '13 at 10:38
I have also appreciated your answer too and made it parameterized. – Smile Dec 12 '13 at 07:48

score 2 · Answer 2 · answered Dec 12 '13 at 07:44

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

score 0 · Answer 3 · answered Nov 26 '13 at 18:58

Use the 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);

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

3 Answers3