current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
    
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 Horácio Junior Apr 4 at 14:30
add comment

3 Answers

up vote 2 down vote 
$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');
share|improve this answer
    
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. –  NullPointer Dec 12 '13 at 7:48
add comment
up vote 0 down vote

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);
share|improve this answer
add comment
up vote 0 down vote

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');
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.