Rails+ActiveStorage：Attachment基于Blob校验和去重及统计问题

我有一个使用ActiveStorage的Rails应用，存在一个Attachment模型，该模型被所有“可附加”模型（如Establishment、Event等）共享。为避免重复上传同一文件，可能存在多个具有相同checksum的Blob。当列出所有附件时需要去除重复项——本质是按关联表active_storage_blobs的checksum字段去重，但最终结果不需要该字段，要保留Attachment表的所有字段。

目前的问题是：用以下SQL能获取去重后的checksum，但改为SELECT DISTINCT active_storage_blobs.checksum, "attachments".*就无法实现去重：

SELECT DISTINCT active_storage_blobs.checksum
FROM ((SELECT "attachments".*
       FROM "attachments"
                INNER JOIN "establishments" ON "attachments"."attachable_id" = "establishments"."id"
                INNER JOIN "establishment_managers"
                           ON "establishments"."id" = "establishment_managers"."establishment_id"
       WHERE "establishment_managers"."manager_id" = 683
         AND "attachments"."attachable_type" = 'Establishment'
       ORDER BY "attachments"."id" ASC)
      UNION
      (SELECT "attachments".*
       FROM "attachments"
                INNER JOIN "events" ON "attachments"."attachable_id" = "events"."id"
                INNER JOIN "establishments" ON "events"."establishment_id" = "establishments"."id"
                INNER JOIN "establishment_managers"
                           ON "establishments"."id" = "establishment_managers"."establishment_id"
       WHERE "establishment_managers"."manager_id" = 683
         AND "attachments"."attachable_type" = 'Event'
       ORDER BY "attachments"."id" ASC)) as attachments
         INNER JOIN "active_storage_attachments" ON "active_storage_attachments"."record_type" = 'Attachment' AND
                                                    "active_storage_attachments"."name" = 'file' AND
                                                    "active_storage_attachments"."record_id" = "attachments"."id"
         INNER JOIN "active_storage_blobs"
                    ON "active_storage_blobs"."id" = "active_storage_attachments"."blob_id"
ORDER BY active_storage_blobs.checksum ASC

另外，使用SELECT DISTINCT ON (active_storage_blobs.checksum) "attachments".*能实现去重，但无法直接用于统计计数。

解决方案 #

1. 获取去重后的Attachment记录

利用PostgreSQL的DISTINCT ON特性，结合CTE筛选出每个checksum对应的唯一Attachment记录：

WITH filtered_attachments AS (
  SELECT "attachments".*
  FROM (
    SELECT "attachments".*
    FROM "attachments"
    INNER JOIN "establishments" ON "attachments"."attachable_id" = "establishments"."id"
    INNER JOIN "establishment_managers" ON "establishments"."id" = "establishment_managers"."establishment_id"
    WHERE "establishment_managers"."manager_id" = 683
      AND "attachments"."attachable_type" = 'Establishment'
    UNION
    SELECT "attachments".*
    FROM "attachments"
    INNER JOIN "events" ON "attachments"."attachable_id" = "events"."id"
    INNER JOIN "establishments" ON "events"."establishment_id" = "establishments"."id"
    INNER JOIN "establishment_managers" ON "establishments"."id" = "establishment_managers"."establishment_id"
    WHERE "establishment_managers"."manager_id" = 683
      AND "attachments"."attachable_type" = 'Event'
  ) AS attachments
  INNER JOIN "active_storage_attachments" ON 
    "active_storage_attachments"."record_type" = 'Attachment' 
    AND "active_storage_attachments"."name" = 'file' 
    AND "active_storage_attachments"."record_id" = "attachments"."id"
  INNER JOIN "active_storage_blobs" ON "active_storage_blobs"."id" = "active_storage_attachments"."blob_id"
)
SELECT DISTINCT ON (asb.checksum) fa.*
FROM filtered_attachments fa
INNER JOIN "active_storage_attachments" asa ON 
  asa."record_type" = 'Attachment' 
  AND asa."name" = 'file' 
  AND asa."record_id" = fa."id"
INNER JOIN "active_storage_blobs" asb ON asb."id" = asa."blob_id"
ORDER BY asb.checksum, fa.id ASC;

如果用Rails查询语法（更符合框架习惯）：

# 先筛选出符合条件且去重后的Attachment ID
unique_attachment_ids = Attachment.joins(<<-SQL)
  INNER JOIN active_storage_attachments asa 
    ON asa.record_type = 'Attachment' 
    AND asa.name = 'file' 
    AND asa.record_id = attachments.id
  INNER JOIN active_storage_blobs asb 
    ON asb.id = asa.blob_id
SQL
.where(<<-SQL, manager_id: 683)
  (
    attachable_type = 'Establishment' 
    AND EXISTS (
      SELECT 1 FROM establishments e 
      INNER JOIN establishment_managers em ON e.id = em.establishment_id 
      WHERE e.id = attachments.attachable_id 
        AND em.manager_id = :manager_id
    )
  ) OR (
    attachable_type = 'Event' 
    AND EXISTS (
      SELECT 1 FROM events ev 
      INNER JOIN establishments e ON ev.establishment_id = e.id 
      INNER JOIN establishment_managers em ON e.id = em.establishment_id 
      WHERE ev.id = attachments.attachable_id 
        AND em.manager_id = :manager_id
    )
  )
SQL
.select("DISTINCT ON (asb.checksum) attachments.id")
.order("asb.checksum, attachments.id ASC")

# 获取完整的Attachment记录
unique_attachments = Attachment.where(id: unique_attachment_ids)

2. 统计去重后的数量

直接统计唯一checksum的数量即可，不需要先查询所有记录：

WITH filtered_attachments AS (
  SELECT "attachments".*
  FROM (
    SELECT "attachments".*
    FROM "attachments"
    INNER JOIN "establishments" ON "attachments"."attachable_id" = "establishments"."id"
    INNER JOIN "establishment_managers" ON "establishments"."id" = "establishment_managers"."establishment_id"
    WHERE "establishment_managers"."manager_id" = 683
      AND "attachments"."attachable_type" = 'Establishment'
    UNION
    SELECT "attachments".*
    FROM "attachments"
    INNER JOIN "events" ON "attachments"."attachable_id" = "events"."id"
    INNER JOIN "establishments" ON "events"."establishment_id" = "establishments"."id"
    INNER JOIN "establishment_managers" ON "establishments"."id" = "establishment_managers"."establishment_id"
    WHERE "establishment_managers"."manager_id" = 683
      AND "attachments"."attachable_type" = 'Event'
  ) AS attachments
  INNER JOIN "active_storage_attachments" ON 
    "active_storage_attachments"."record_type" = 'Attachment' 
    AND "active_storage_attachments"."name" = 'file' 
    AND "active_storage_attachments"."record_id" = "attachments"."id"
  INNER JOIN "active_storage_blobs" ON "active_storage_blobs"."id" = "active_storage_attachments"."blob_id"
)
SELECT COUNT(DISTINCT asb.checksum) AS unique_count
FROM filtered_attachments fa
INNER JOIN "active_storage_attachments" asa ON 
  asa."record_type" = 'Attachment' 
  AND asa."name" = 'file' 
  AND asa."record_id" = fa."id"
INNER JOIN "active_storage_blobs" asb ON asb."id" = asa."blob_id";

用Rails的话直接对之前的unique_attachment_ids计数：

unique_attachments_count = unique_attachment_ids.count

说明 #

• DISTINCT ON是PostgreSQL专属语法，必须将去重字段放在ORDER BY的首位，这里按checksum排序后，取每个分组下最早创建的Attachment（按id升序）
• 统计数量时，COUNT(DISTINCT checksum)直接计算唯一值的数量，效率比查询所有记录再计数更高
• Rails查询方式避免了硬写复杂SQL，同时保留了框架的可维护性

内容的提问来源于stack exchange，提问作者brcebn