Rails find - order by presence of Paperclip attachment then record creation datetime

I am building a gallery index of Items in which some Items have photos and others do not.

I would like to grab all of the Items (or a subset with will_paginate) and sort them first by the items with a photo (attached via Paperclip) and then by the record creation date.

I need a binary "has attachment" type status for the first part. Do I need to make some additional model attributes and use paperclip's callba开发者_Python百科cks to set the status of a binary column? Or is there a better way?

I would prefer to do this sorting at the DB level since we will be using will_paginate to cycle through blocks of records.

thanks in advance

You shouldn't need to create an additional field, instead convert the filename to boolean on the fly with CONV (I'm making an assumption you are running this on MySQL - but equivalent functions are available on other RDBMS).

ORDER BY CONV(photo_file_name,2,2) DESC, created_at

Technically, CONV is a function to convert numbers between different bases, but it accepts string input. It will return 0 if a value is present in the photo_file_name column and NULL if NULL input.

MySQL CONV Reference

Because we are suing SQLite in dev and postgres in production (and may switch production soon) CONV() wasn't available.

We went with doing a comparison in the ORDER BY clause to create the binary value needed.

ORDER BY (photo_file_name IS NULL) ASC, created_at DESC 

This appears to be supported by all our DBs and accomplishes the intended goal.