How to query records that have an ActiveStorage attachment?

Question

Given a model with ActiveStorage

class User 
  has_one_attached :avatar
end

I can check whether a single user has an avatar

@user.avatar.attached? 

But how can I return a collection of all users with (or all users without) an attachment?

I tried using joins to return all Users with an attachment, but this does not seem to work on either the blob or attachment table, or perhaps I'm not getting the syntax correct.

I'm sure I am overlooking something obvious. Is it possible to do something along the lines of:

User.where(attached_avatar: nil)

And if so, where is this documented?

Didst

Answer 1

Convention for attachment association names

Attachment associations are named using the following convention:

<NAME OF ATTACHMENT>_attachment

For example, if you have has_one_attached :avatar then the association name will be avatar_attachment.

Querying for Active Storage attachments

Now that you know how attachment associations are named, you can query them by using joins as you would any other Active Record association.

For example, given the User class below

class User
   has_one_attached :avatar
end

You can query for all User records that have that attachment as follows

User.joins(:avatar_attachment)

This performs an INNER JOIN which will only return records which have the attachment.

You can query for all User records that DO NOT have that attachment like this

User.
  left_joins(:avatar_attachment).
  group(:id).
  having("COUNT(active_storage_attachments) = 0")

Hast

The having clause of the last query was not working for me. Instead, I used User.left_joins(:avatar_attachment).where('active_storage_attachments.id is NULL') – Horgan

... with pure ActiveRecord DSL of the solution from the commenter before me: User.left_joins(:avatar_attachment).where(active_storage_attachments: { id: nil }) – Pilgarlic

For multiple images, it's .left_joins(:avatars_attachments) (plural on avatars, and on attachments) – Counterclockwise

Answer 2

I wanted to know if a record has any attachments (I had multiple attachments say a passport and other docs for User) so I can display/hide a section in UI.

Based on the answer here I've been able to add the following method to ApplicationRecord:

def any_attached?
  ActiveStorage::Attachment.where(record_type: model_name.to_s, record_id: id).any?
end

Then you can use it like:

User.last.any_attached?
#=> true

Acromion

Answer 3

Query records WITH attachment:

User.joins(:avatar_attachment)

Query records WITHOUT attachment:

User.includes(:avatar_attachment).where(avatar_attachment: {id: nil})

Toothbrush

Answer 4

Slightly related, here is how to perform a search query on the attached records:

def self.search_name(search)
  with_attached_attachment.
    references(:attachment_attachment).
    where(ActiveStorage::Blob.arel_table[:filename].matches("%#{search}%"))
end

You'll just have to update the with_attached_attachment and :attachment_attachment to reflect your attached model. In my case I have has_one_attached :attachment

And for those wondering, the Arel #matches does not appear to be susceptible to SQL injection attacks.

Raven

Answer 5

This is how to query user records that have that attachment in SQL:

User.where(<<~SQL)
  EXISTS (
    SELECT 1 FROM active_storage_attachments
    WHERE active_storage_attachments.record_type = 'User' AND
    active_storage_attachments.name = 'avatar' AND
    active_storage_attachments.record_id = users.id::text
  )
SQL

Shameful