0

This should be a fairly straightforward question, but I haven't been able to locate any answers online regarding it. I have the following in Magento:

$consultantCollection->setOrder('firstname', 'DESC'); 

Which translates in MySQL to:

ORDER BY `firstname` DESC;

What I actually want is to be able to sort by a specific 'firstname' value. In MySQL, this is what I'd like to accomplish:

ORDER BY `firstname`='Peter' DESC;

This way, I can pull a list of 100 people with the same last name, but get any with a specified first name to appear first in the result set.

Unfortunately, I haven't been able to locate any documentation online as to how to achieve this. I know it's an edge case, but it's rather infuriating to have nothing in the Magento docs on how to do something that SQL can handle natively without any hassle.

Insight appreciated!

2 Answers 2

1

Try

$consultantCollection->getSelect()->order(new Zend_Db_Expr("`firstname`='Peter' DESC"));

Should result in

Select ....  ORDER BY `firstname`='Peter' DESC

Test Code

$customerCollection = Mage::getModel('customer/customer')->getCollection();
$customerCollection->addNameToSelect();
$customerCollection->addAttributeToSelect(array('firstname', 'lastname', 'email'));
$customerCollection->getSelect()->order(new Zend_Db_Expr("`firstname`='Peter' DESC"));

foreach ($customerCollection as $customer) {
/* @var $customer Mage_Customer_Model_Customer */
echo $customer->getFirstname() . ' ' . $customer->getLastname() . PHP_EOL;
}
       // 

echo $customerCollection->getSelect();
2

You can add a new column to the select that has values 1 and 0 depending on the condition you need

$collection = Mage::getModel('[module]/[entity]')->getCollection()
    ->addExpressionAttributeToSelect('is_peter', 'IF ({{firstname}} = 'peter', 1, 0) array('{{firstname}}'=>'firstname'));

Then just sort by that field.

$collection->getSelect()->order('is_peter DESC');
2
  • Great answer, way to think outside the box! Would upvote if I had more reputation.
    – carbide20
    Commented Oct 6, 2014 at 22:20
  • Hello what should we write instead of '[module]/[entity]'? Commented May 28, 2018 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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