CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-6rails-activestorage
stevec
stevec

Reputation: 52218

Query for all users who have ".variable?" Active Storage image attachment(s)?

I read this helpful answer on How to query records that have an ActiveStorage attachment?

I am trying to query for all Users who have at least one image (easy to adapt related question, see below), and whose first image is .variable?

What I know so far

This returns all users who have at least one image

User.
  left_joins(:images_attachments).
  group(:id).
  having("COUNT(active_storage_attachments) > 0")

But I'm not sure how to return just the users whose first image is .variable? (i.e. users whose image won't error when its size is changed)

Upvotes: 0

Views: 623

Answers (2)

eux
eux

Reputation: 3282

I am trying to query for all Users who have at least one image (easy to adapt related question, see below), and whose first image is .variable?

You want Users who have at least one image, so INNER JOIN should be ok.

If you want whose first image literally, this could by achieved by join subquery.

And combine the answer of @SebastianPalma, we could do it like this:

# Query for attachments with just first image of users
subquery = ActiveStorage::Attachment.where(record_type: 'User')
                                    .order(:record_id, created_at: :asc)
                                    .select('DISTINCT ON (record_id) *')
                                    .to_sql

User.joins("INNER JOIN (#{subquery}) AS active_storage_attachments ON active_storage_attachments.id = users.id")
    .joins('INNER JOIN active_storage_blobs ON active_storage_blobs.id = active_storage_attachments.blob_id')
    .where(active_storage_blobs: { content_type: ActiveStorage.variable_content_types })

Upvotes: 1

Sebastián Palma
Sebastián Palma

Reputation: 33420

As variable? is defined in the ActiveStorage::Blob::Representable module, you should load every ActiveStorage::Blob object and check what's the value the method returns when invoked on it. But I think that could remain as the last alternative.

Knowing that what the variable? method does is just to check whether the blob content_type is one of the predefined ones in ActiveStorage.variable_content_types, you could try doing that, but using SQL:

User.left_joins(:main_image_attachment)
    .joins('INNER JOIN active_storage_blobs ON active_storage_blobs.id = active_storage_attachments.blob_id')
    .group(:id)
    .having('COUNT(active_storage_attachments.id) > 0')
    .where(active_storage_blobs: { content_type: ActiveStorage.variable_content_types })

So you do almost the same, but just bring the active_storage_blobs table into memory to get access to the content_type column.

Upvotes: 3

Related Questions