I have a custom collection for my extension and am trying to implement search functionality. This would be pretty easy if I was using regular MySQL expressions but can't seem to get it working with Magento's available collection methods.
Here is the code I have:
/** @var Pvtl_Consult_Model_Resource_Consultations_Collection $customers_collection */ $customers_collection = Mage::getModel('pvtl_consult/consultations')->getCollection(); $customers_collection ->join('pvtl_consult/consultants', 'consult_consultant = consultant_id') ->addFieldToFilter('consult_lastname', array( array('like' => $search), array('like' => '%' . $search), array('like' => $search . '%'), array('like' => '%' . $search . '%') ) ) ->addFieldToFilter('consultant_store', $store_id); $customers_collection->getSelect()->order( "CASE WHEN 'consult_lastname' = '$search' THEN 1 WHEN 'consult_lastname' = '%$search' THEN 2 WHEN 'consult_lastname' = '$search%' THEN 3 WHEN 'consult_lastname' = '%$search%' THEN 4 ELSE 5 END" );
After debugging the select statement created from above, I notice it is failing due to Magento inserting backticks around the
Is there a way to prevent this? Or, even better, is there a more optimised way to order by case in Magento?