Zend Framework – Magento/Zend_Db Order By Expression

MySQLqueryzend-framework

Take this code for example:

$collection->getSelect()->order(array('status = 1 DESC', 'test ASC'));

Magento/Zend_Db turns this into the following:

ORDER BY `status = 1` DESC, `test` ASC

You can see this with Zend_Debug::dump($collection->getSelect()->__toString());

Notice that it wraps status = 1 with ` characters, as if it were escaping a field? This breaks the query. Is there any way to order by expression in Magento/Zend_Db?

Best Answer

I would think new Zend_Db_Expr would do what you need.

$collection->getSelect()->order(array(new Zend_Db_Expr('status = 1 DESC'), 'test ASC'))

Details:

https://stackoverflow.com/questions/9957584/magento-collection-expression

Related Solutions

Magento – Using “INSERT IGNORE INTO” in MySQL

You could create a unique constraint which covers all three fields, so an Insert would not create a new entry.

https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql

Database – Setting Collection Sort Order to a Specific Value

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

Best Answer

Related Solutions

Magento – Using “INSERT IGNORE INTO” in MySQL

Database – Setting Collection Sort Order to a Specific Value

Related Topic