Rails associations循环问题：关联记录数可变时的数据展示最佳实践

• 表结构冗余：shop_one/shop_two/shop_three三个表字段完全重复，后续新增门店需要额外加表加关联，维护成本极高
• 关联配置错误：ShoppingTrip中配置的belongs_to :user是指行程所属的单个用户，和各门店的购买用户没有关联，所以直接调用s.user只会返回固定的行程所属用户，无法拿到对应购买行为的用户
• 查询逻辑错误：当前left_joins多个门店表会产生笛卡尔积导致数据重复，且select仅取门店表字段，没有关联对应用户信息

调整Schema #

将三个门店表合并为统一的购买记录表，新增字段区分门店：

create_table "shopping_trips", force: :cascade do |t|
  t.string "location"
  t.date "date"
end

create_table "users", force: :cascade do |t|
  t.string "name"
end

# 替代原三个门店表的统一购买记录表
create_table "shop_purchases", force: :cascade do |t|
  t.integer "shopping_trip_id"
  t.integer "user_id"
  t.string "shop_name" # 存储shop_one/shop_two/shop_three区分门店
  t.string "item_purchased"
  t.decimal "item_price"
end

修正关联配置 #

class ShoppingTrip < ApplicationRecord
  # 移除错误的belongs_to :user配置
  has_many :shop_purchases, dependent: :destroy
  has_many :users, through: :shop_purchases # 关联所有参与本次行程购买的用户
  accepts_nested_attributes_for :shop_purchases, allow_destroy: true
end

class ShopPurchase < ApplicationRecord
  belongs_to :shopping_trip
  belongs_to :user
end

class User < ApplicationRecord
  has_many :shop_purchases
end

Controller查询逻辑 #

def index
  # 预加载关联数据避免N+1问题
  @shopping_trips = ShoppingTrip.includes(shop_purchases: :user).all
end

视图渲染逻辑 #

按用户+门店分组统计，即可实现预期的表格展示效果：

<% @shopping_trips.each do |trip| %>
<table>
  <thead>
    <tr>
      <th>用户名</th>
      <th>Shop one购买数量</th>
      <th>Shop two购买数量</th>
      <th>Shop three购买数量</th>
    </tr>
  </thead>
  <tbody>
    <% trip.users.distinct.each do |user| %>
    <tr>
      <td><%= user.name %></td>
      <td><%= trip.shop_purchases.where(user: user, shop_name: 'shop_one').count %></td>
      <td><%= trip.shop_purchases.where(user: user, shop_name: 'shop_two').count %></td>
      <td><%= trip.shop_purchases.where(user: user, shop_name: 'shop_three').count %></td>
    </tr>
    <% end %>
  </tbody>
</table>
<% end %>

如果暂时无法重构表结构，可以按照以下逻辑调整：

1. 给ShopOne/ShopTwo/ShopThree三个模型都加上belongs_to :user关联
2. 删除ShoppingTrip中错误的belongs_to :user配置
3. Controller预加载关联数据：

def index
  @shopping_trips = ShoppingTrip.includes(shop_ones: :user, shop_twos: :user, shop_threes: :user).all
end

4. 视图层先收集所有参与购买的用户再统计：

<% @shopping_trips.each do |trip| %>
<% 
  # 合并三个门店的用户去重
  all_users = (trip.shop_ones.map(&:user) + trip.shop_twos.map(&:user) + trip.shop_threes.map(&:user)).uniq.compact
%>
<table>
  <thead>
    <tr>
      <th>用户名</th>
      <th>Shop one购买数量</th>
      <th>Shop two购买数量</th>
      <th>Shop three购买数量</th>
    </tr>
  </thead>
  <tbody>
    <% all_users.each do |user| %>
    <tr>
      <td><%= user.name %></td>
      <td><%= trip.shop_ones.where(user: user).count %></td>
      <td><%= trip.shop_twos.where(user: user).count %></td>
      <td><%= trip.shop_threes.where(user: user).count %></td>
    </tr>
    <% end %>
  </tbody>
</table>
<% end %>

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