CakePHP Find - Order By String-To-Int?

Question

I want to use CakePHP to pull an array of photos from a database, sorted by photo title (0, 1, 2, 3...) My query currently looks something like:

$ss_photos = $this->Asset->find('all',array(
  'conditions'=>array('kind'=>'photo'), 
  'order'=>'title'
));

Unfortunately the titles seem to be in string format, leading to an undesirable sort order (2.jpg after 19.jpg, etc). Is there a quick way to cast 'title' as an int for ordering purposes within a Cake query of this type?

Answer 1

The solution is to create a hidden column which is responsible for orders in your example image names should be: 00002.jpg, 00019.jpg - this way the order will work properly.

If the results are not too many, I think it's easier to sort them in PHP (if you use it of course :)) See this natsort() you just need to extract a list of images and to sort them.

Answer 2

Not sure if this is "recommended practice", but on a first pass it seems to work:

$ss_photos = $this->Asset->find('all',array(
    'conditions'=>array('kind'=>'photo'), 
    'order'=>'Asset.title + 0'
));

Any opinions?