'How to query records that have an ActiveStorage attachment?

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?

ruby-on-railsrails-activestorage


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

Query records WITH attachment:

User.joins(:avatar_attachment)

Query records WITHOUT attachment:

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 M-Dahab
Solution 2 bfcoder
Solution 3 Jeremy Lynch

Related Questions