Rails find - the order of presence of the Paperclip application, and then the datetime creation record

Question

I am creating an item gallery index in which some elements have photos and others do not.

I would like to capture all the elements (or a subset with will_paginate) and sort them first by photo elements (attached via Paperclip), and then by the date the record was created.

I need to have a binary status of type “attachment” for the first part. Do I need to create additional model attributes and use paperclip callbacks to set the status of a binary column? Or is there a better way?

I would prefer to do this sorting at the database level, since we will use will_paginate for the write block cycle.

early

Answer 1

Since we are judging SQLite in dev and postgres in production (and may soon change production) CONV () was not available.

We went with the comparison in the ORDER BY clause to create the required binary value.

ORDER BY (photo_file_name IS NULL) ASC, created_at DESC 

This, apparently, is supported by all our databases and fulfills the intended purpose.

Answer 2

You do not need to create an additional field, but instead convert the file name to boolean on the fly using CONV (I assume you use this in MySQL), but equivalent functions are available on other RDBMSs).

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

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

MySQL CONV