Zend Framewrok 2: How to write custom query using tablegateway

Question

I need to write a update query in a model which use tablegateway. The update query sql like following.

UPDATE `config_settings` SET `CS_value` = CASE `CS_option`
    WHEN 'CATEGORY_BANNER_MIN_WIDTH' THEN '$data->CATEGORY_BANNER_MIN_WIDTH'
    WHEN 'CATEGORY_BANNER_MAX_WIDTH' THEN '$data->CATEGORY_BANNER_MAX_WIDTH'
    WHEN 'CATEGORY_PROMOTION_MIN_WIDTH' THEN '$data->CATEGORY_PROMOTION_MIN_WIDTH'
    WHEN 'CATEGORY_PROMOTION_MAX_WIDTH' THEN '$data->CATEGORY_PROMOTION_MAX_WIDTH'
    WHEN 'PRODUCT_LARGE_IMAGE_WIDTH' THEN '$data->PRODUCT_LARGE_IMAGE_WIDTH'
    WHEN 'PRODUCT_MEDIUM_IMAGE_WIDTH' THEN '$data->PRODUCT_MEDIUM_IMAGE_WIDTH'
    WHEN 'PRODUCT_SMALL_IMAGE_WIDTH' THEN '$data->PRODUCT_SMALL_IMAGE_WIDTH'
    ELSE `CS_value`
END;

I have no idea how to implement this. The update method of tablegateway only take array of table field name and its value. So how to write this query.

I know that i can execute this query using db adapter raw sql query but i don't want this. Beside this, some times we need to some custom query in select method of tablegateway. But i found no stable way in tablegateway.

For example:

select sum(CASE WHEN answers.type = 'his' THEN 1 ELSE 3 END) AS totalScore  
FROM users_questions_answers join answers on cast(answers.id as int(8))= 
users_questions_answers.answer_id group by users_questions_answers.user_id

How can i proceed in this situation. Any zend 2 expert suggestion will highly appreciated. Thanks for your kind consideration.

Kunal Dethe Kunal Dethe · Accepted Answer · 2014-06-17T10:13:24

Try this -

$this->tablegateway->update(array('CS_value' => new \Zend\Db\Sql\Expression('CASE CS_option
    WHEN "CATEGORY_BANNER_MIN_WIDTH" THEN ?
    WHEN "CATEGORY_BANNER_MAX_WIDTH" THEN ?
    WHEN "CATEGORY_PROMOTION_MIN_WIDTH" THEN ?
    WHEN "CATEGORY_PROMOTION_MAX_WIDTH" THEN ?
    WHEN "PRODUCT_LARGE_IMAGE_WIDTH" THEN ?
    WHEN "PRODUCT_MEDIUM_IMAGE_WIDTH" THEN ?
    WHEN "PRODUCT_SMALL_IMAGE_WIDTH" THEN ?
    ELSE CS_value END', 
    array($data->CATEGORY_BANNER_MIN_WIDTH, $data->CATEGORY_BANNER_MAX_WIDTH, $data->CATEGORY_PROMOTION_MIN_WIDTH, $data->CATEGORY_PROMOTION_MAX_WIDTH, $data->PRODUCT_LARGE_IMAGE_WIDTH, $data->PRODUCT_MEDIUM_IMAGE_WIDTH, $data->PRODUCT_SMALL_IMAGE_WIDTH))));

If CATEGORY_BANNER_MIN_WIDTH like used in the query are some constants then just remove the double quotes around it.

I have tried a similar query to the above one and it works just fine.

Zend Framewrok 2: How to write custom query using tablegateway

1 Answers