0

I try to extend Abandoned Carts report. In order to do that I need to modify a query that is used to populate the grid. I can't prepare coalesce condition using Varien_Db_Select.

Now there is:

// Mage_Reports_Model_Resource_Quote_Collection::addCustomerData($filter = null)
$this->getSelect()
    ->joinLeft(
        array('cust_email' => $attrEmailTableName),
        'cust_email.entity_id = main_table.customer_id',
        array('email' => 'cust_email.email')
    )
//...
$this->_joinedFields['email']         = 'cust_email.email';
//...

// Mage_Adminhtml_Block_Report_Shopcart_Abandoned_Grid::_prepareColumns()
$this->addColumn('email', array(
    'header'    =>Mage::helper('reports')->__('Email'),
    'index'     =>'email',
    'sortable'  =>false
));

This is the query:

SELECT `main_table`.*, (main_table.base_subtotal_with_discount*main_table.base_to_global_rate) AS `subtotal`, `cust_email`.`email`, `cust_fname`.`value` AS `firstname`, `cust_mname`.`value` AS `middlename`, `cust_lname`.`value` AS `lastname`, CONCAT_WS(' ', cust_fname.value, cust_mname.value, cust_lname.value) AS `customer_name`
FROM `sales_flat_quote` AS `main_table`
LEFT JOIN `customer_entity` AS `cust_email` ON cust_email.entity_id = main_table.customer_id
LEFT JOIN `customer_entity_varchar` AS `cust_fname` ON cust_fname.entity_id = main_table.customer_id AND cust_fname.attribute_id = 1
LEFT JOIN `customer_entity_varchar` AS `cust_mname` ON cust_mname.entity_id = main_table.customer_id AND cust_mname.attribute_id = 827
LEFT JOIN `customer_entity_varchar` AS `cust_lname` ON cust_lname.entity_id = main_table.customer_id AND cust_lname.attribute_id = 2
WHERE (`items_count` != '0') AND (`main_table`.`is_active` = '1')

I would like to have in column email:

COALESCE(cust_email.email, main_table.customer_email)
0

I had the same issue and after some digging came up with this as the solution:

$this->getSelect()->columns('COALESCE(cust_email.email, main_table.customer_email)');

https://stackoverflow.com/questions/8433176/select-only-specific-fields-in-magento

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.