0

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 CASE expression.

Is there a way to prevent this? Or, even better, is there a more optimised way to order by case in Magento?

1

I managed to get this fixed. For anyone interested, the code I used was:

$customers_collection->getSelect()->order(
    new Zend_Db_Expr(
        "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"
    )
);

Really hope this helps others!

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.