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

Question

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 callbacks 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

Answer 1

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

Answer 2

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.