Code snippets for symfony 1.x

Navigation

Custom ORDER BY with Criteria

Hello everyone,

i accidently found a neat feature of MYSQL on the web, which isn't even documented in the official MySQL 5.x manual (original blog entry to be found at http://www.cfdan.com/posts/Handy_MySQL_-_ORDER_BY_FIELD.cfm">original Blog Posting)

Basicly i needed a way to create some custom order for my records, so i started to build a raw SQL query. Then i ran into troubles when i tried to get this working with a pager.. all in all, i tried it to solve it with Criteria.

I created a new class, myCriteria which inherits from the original Criteria class.

/**
 * Add order by column name with a custom Order for a given Field
 *
 * @param unknown_type $propertyBracket
 * @return unknown
 */
public function addCustomOrderByColum($propertyField, $propertyList) {
    $this->orderByColumns[] = 'FIELD('.$propertyField.', ' . $propertyList .' )';
    return $this;
}
 

An example could look like:

$c->addCustomOrderByColum(CustomPeer::TYPE, " 'Sold', 'Pending', 'Announced' ");
 

This way you can create ORDER BY Statements which are not bound to common ASC/DESC rules.

by Christian Weyand on 2007-12-12, tagged criteria  custom  mysql 

Comments on this snippet

gravatar icon
#1 Eric Delord on 2007-12-15 at 07:13

After looking at the code myself, I found that you may in fact already to this:

$c = new Criteria(); $c->addAscendingOrderByColumn( sprintf("FIELD(%s,%s)", CustomPeer::ID, "1,5,3,7") );

works! so no need to extends Criteria in fact.

gravatar icon
#2 Christian Weyand on 2007-12-17 at 12:26

You're right, works quite fine.

I ran a test with the addAscendingOrderByColumn() function and ran into trouble with my syntax, i thought this was a result of the appended ASC by this function. Dumb me could have saved quite some time fixing a type instead of writing/testing a new class ;)