Database – Setting Collection Sort Order to a Specific Value

databaseMySQLquery

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!

Best Answer

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();
Related Topic