ActiveRecord.where(column: array_of_strings) - how to preserve order?

Question

This question is a lot like ActiveRecord.find(array_of_ids), preserving order but about using 'where' and an array of ids that are strings.

Our situation is unfortunately a bit more complex where we receive a JSON string (an ArchiveObject has media attached to it through a column, media) such as:

[{"image_id":"ABCD1234", "type":"Image"},
{"image_id":"ABCD2345", "type":"Image"},
{"image_id":"ABCD3456", "type":"Image"}]

Each of these objects do exist in the database, but to make a long story short, we can't use their integer based pk IDs because our database is constantly flushed and rewritten from an export of XML data.

Currently, we are

1. Parsing the JSON
2. Mapping the 'image_id'
3. Performing a where to find the relevant objects

Code:

images = JSON.parse(current_archive_object.media)
image_ids = images.map { |image| image['image_id'] }
images = ArchiveObject.where(image_id: @image_ids)

Thus my question is, is there any way to order the resulting activerecord relation to the original array image_ids? Or if I can loop through image_ids, is there a way to access a particular object in the relation via one of its column values efficiently?

Extra note:

The reason we went through all this trouble of mapping and where is that each image has a caption, url etc. stored in its own row that isn't present in the media column of its parent object, thus to prevent database lookups per image, we tried to compile it all into one DB call.

Answer 1

I'm not sure if my answer isn't postgresql-specific, but in my project I'd the same problem.

Try solution below

images = JSON.parse(current_archive_object.media)
image_ids = images.map { |image| image['image_id'] }
order = "position(image_id::text in '#{image_ids.join(',')}')"
images = ArchiveObject.where(image_id: image_ids).order(order)

Hope it helps.