[How to] Filter by mime type

[Tao-Galasse]

Hello !

I have a table Attachment with different kinds of accepted mime types (let's say image/jpeg image/jpg application/pdf).

I would like to filter them by kind of mime type : for exemple, get every attachments which is an image.

I could use the file_data metadata and write something like this : Attachment.where('file_data ilike ?', '%mime_type\":\"image%') but it seems kinda hacky.

Is there a better way to achieve this ?

Thank you ! 🙏

[janko]

The metadata_attributes plugin was intended for these cases, allowing you to write metadata into separate columns.

[Tao-Galasse]

Ok, so if I add a file_mime_type column on my Attachment model, it will work magically ? ✨

But how to make it retro-active ? I would like to fill this column on my existing attachments records.

[janko]

Yeah, you would need to backfill it for existing records with a data migration. Depending on what DB and data type you're using, it could be a single UPDATE statement, or an UPDATE statement per record.

[Tao-Galasse]

I'm using postgresql. I could simply write Attachment.find_each { |attachment| attachment.update(file_mime_type: attachment.file.mime_type) }, but indeed, it would run one UPDATE query per record.

Do you think about a way to use a single UPDATE statement ?

[janko]

If you're using a text column, you could use JSON functions to parse the file_data column, e.g. something like this:

Attachment.update_all("file_mime_type = file_data::jsonb -> 'metadata' ->> 'mime_type'")

I haven't tested this, so I don't know if it works, but that's the idea. Here are the docs – https://www.postgresql.org/docs/15/functions-json.html.

[Tao-Galasse]

Thank you very much, I will take a look at this.

Thanks again for your help and reactivity! 🚀 🔥