1

I have an issue in Magento 1, where the SQL query that is getting generated has the back tics wrongly placed.

This is a piece of the code that helps to provide the collection to the grid layout.

protected function _prepareCollection(){

    $accountId = $this->getRequest()->getParam('id');
    $collection = Mage::getResourceModel('testmodule/coupon_collection');


    if (Mage::helper('testmodulecoupon')->isMultiProgram()
        && Mage::helper('core')->isModuleEnabled('Test_module')
    ){
        $collection->getSelect()->joinLeft(
            array('p'   => $collection->getTable('testmodule/account')),
            'main_table.program_id = p.program_id AND main_table.account_id = p.account_id',
            array()
        )->joinLeft(
            array('r'   => $collection->getTable('testmodule/program')),
            'main_table.program_id = r.program_id',
            array('status' => 'IF (main_table.program_id = 0, 1, IF(p.id AND r.use_coupon, 1, 0))')
        );
    } else {
        $collection->addFieldToFilter('program_id', array('eq'=>0));
    }
    $collection->addFieldToFilter('main_table.account_id',$accountId);  

    $this->setCollection($collection);
    return parent::_prepareCollection();
}

In the above code, this part of the line is generating the back tics wrongly.

array('status' => 'IF (main_table.program_id = 0, 1, IF(p.id AND r.use_coupon, 1, 0))') 

This is part of SQL query that is generating.

(`IF (main_table`.`program_id = 0, 1, IF(p`.`id AND r`.`use_coupon, 1, 0))` = '1')

I was not able to find a proper solution to fix this. Can anyone help on this ? thanks

1

Try to build that part of the query using Zend_Db_Expr object. This way should work correctly:

array('status' => new Zend_Db_Expr('IF (main_table.program_id = 0, 1, IF(p.id AND r.use_coupon, 1, 0))'))
  • Thanks HelgeB ! it worked without errors. – kaushik kumar roy Mar 26 at 7:25
  • Great to here that it works for you. If the answer was helpful it would be nice if you would upvote and accept it. That way others would easier find the soution for similar problems. – HelgeB Mar 26 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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