Zend Db order by custom list

I'd like to sort some rows of my database on a custom list with Zend Db.

They have the field page_type, which can be 0, 1, 2 or 3 and I'd like them in this order: 1,3,2,0

I have found that it is possible with MySQL:

SELECT id 
FROM table 
WHERE id IN (15,21,4,8) 
ORDER BY id = 15 DESC, id = 21 DESC, id = 4 DESC, id = 8 DESC

Is it also possible with Zend Db or should I just use plain MySQL here?

This is my code now:

$select = $this->select()
    ->order(array(
        'asc' => 'page_type'
    ));

Solution

It would be possible to change page_type to be ENUM('1','3','2','0') (pay attention to the order). Internally MySQL will link index 0 to value 1, index 1 to value 3, etc etc.

If you then order by page_type ASC it will order in the order you'd like it to as it orders by the ENUM key rather than the actual text which each key relates to.

Depending on what page_type actually links to you could always use text descriptors instead i.e. ENUM('news','something else',.....) - you've provided no information on what that links to so cannot advise further on that.

More information on enum can be found here : http://dev.mysql.com/doc/refman/5.0/en/enum.html (scroll down to where the paragraph starts "ENUM values are sorted according to "....

The only down side is that you will need to update the table schema every time you add a new page_type, however I would assume due to the field name this won't be a common occurance so this could be a viable option