通过关联中的自定义查询避免N+1查询问题

你遇到的核心问题是：关联中使用带动态参数的lambda会导致includes无法批量预加载——ActiveRecord没法提前为所有用户生成通用的预加载查询，只能在遍历每个用户时单独发起查询，从而触发N+1。下面是两种可行的重构方案：

方案1：重构关联为批量可预加载模式 #

直接修改User模型的关联定义，移除动态参数，通过JOIN关联用户的offers数据，让ActiveRecord能一次性加载所有用户的分类及对应projects_count：

class User < ApplicationRecord
  has_many :categories_with_projects_count, -> {
    select(
      'categories.*, 
       COALESCE(won_projects_stats.count, 0) AS projects_count'
    )
    .joins(<<~SQL.squish)
      LEFT JOIN (
        SELECT 
          cat.parent_id AS parent_category_id, 
          cat.id AS category_id,
          offers.user_id,
          COUNT(*) AS count
        FROM projects
        INNER JOIN categories_projects ON categories_projects.project_id = projects.id
        INNER JOIN categories cat ON categories_projects.category_id = cat.id
        INNER JOIN offers ON projects.id = offers.project_id
        WHERE offers.state = 'accepted'
        GROUP BY cat.parent_id, cat.id, offers.user_id
      ) AS won_projects_stats ON (
        categories.id = won_projects_stats.parent_category_id
        OR categories.id = won_projects_stats.category_id
      ) AND won_projects_stats.user_id = users.id
    SQL
  }, through: :user_profile, source: :categories
end

# 可以移除Category中原来的with_projects_count scope，或保留作其他用途

修改后直接使用原有的includes代码即可：

@users = User.includes(categories_with_projects_count: [:users_seo_tagging_page, :parent, { logo_attachment: :blob }])

这种方案通过won_projects_stats.user_id = users.id将统计数据与当前用户绑定，无需动态传参，ActiveRecord能一次性完成预加载，彻底解决N+1问题。

方案2：预加载时使用子查询计算统计值 #

如果不想大幅修改关联结构，也可以在预加载时用子查询直接计算projects_count：

@users = User.includes(
  categories_with_projects_count: -> {
    select(
      "categories.*, 
       COALESCE((
         SELECT COUNT(*)
         FROM projects
         INNER JOIN categories_projects ON categories_projects.project_id = projects.id
         INNER JOIN offers ON projects.id = offers.project_id
         WHERE 
           offers.state = 'accepted'
           AND offers.user_id = users.id
           AND (categories_projects.category_id = categories.id OR categories_projects.category_id = categories.parent_id)
       ), 0) AS projects_count"
    )
  }
)

这种方式无需修改原有关联，逻辑更直观，但子查询的性能可能略逊于方案1的JOIN方式，适合数据量不大的场景。

重要注意事项 #

1. 避免直接在SQL中硬编码#{user_id}，改用关联条件或占位符，防止SQL注入风险；
2. 确保GROUP BY字段与SELECT中的非聚合字段完全匹配，避免PostgreSQL等数据库的严格模式报错；
3. 可以通过查看Rails日志验证效果：预加载生效后只会出现2次查询（一次查用户，一次查所有用户的分类及统计），没有额外的N+1查询。

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