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

Question

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)

@SebastianPalma I'm not exactly sure, it's not mentioned in the [Rails Guide](https://edgeguides.rubyonrails.org/active_storage_overview.html) on Active Storage, however, there's a basic description [here](https://stackoverflow.com/a/61971660/5783745) (basically it means rails can change the size of the image - for example if someone accidentally uploaded a `.pdf` as their profile pic, then `.variable?` would return false, but for most image types, it returns true) — stevec, Feb 21 '21 at 15:20

Sebastián Palma · Answer 1 · 2021-02-21T23:08:07.473

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.

Doesn't the INNER JOIN guarantee `'COUNT(active_storage_attachments.id) > 0'`? — engineersmnky, Feb 22 '21 at 18:49
Indeed, good catch, but you'll need to add the joins with `main_image_attachment` as well to make it work. — Sebastián Palma, Feb 22 '21 at 19:28

eux · Answer 2 · 2021-02-22T07:05:13.897

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 })

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

What I know so far

2 Answers2