Ecto技术问题：如何在Elixir Postgres Schema中填充商家信息列表

问题场景 #

使用Ecto Schema进行多表关联查询时，返回结果中item的business_ids仅为字符串数组，无法获取每个item对应的商家详细信息列表，期望返回包含商家完整数据的结构。

原代码及定义 #

查询语句 #

query =
  from(
    item in ItemSchema,
    join: item_cat in ItemCategorySchema,
    on: item.item_category_id == item_cat.item_category_id,
    preload: [:BusinessSchema],
    select: %{
      "item_id" => item.item_id,
      "item_name" => item.item_name,
      "description" => item.description,
      "item_category" => item_cat.category_name,
      "businesses" => item.business_ids,
    }
  )

Repo.all(query)

Item Schema #

schema "items" do
  field(:item_id, :string)
  field(:item_category_id, :string)
  field(:user_id, :string)
  field(:business_ids, {:array, :string})

  field(:item_name, :string)
  field(:description, :string)
end

Item Category Schema #

schema "item_categories" do
  field(:item_category_id, :string)
  field(:item_category_id, :string) # 存在重复定义字段问题
  field(:category_name, :string)
end

Business Schema #

schema "businesses" do
  field(:business_id, :string)
  field(:business_name, :string)
end

期望返回结果 #

[
  %{
      "item_id" => "id_01",
      "item_name" => "A",
      "description" => "A description",
      "item_category" => "some category",
      "businesses" => [
          %{
              "business_id" => "business_id_01",
              "business_name" => "Business Name 1",
          },
          %{
              "business_id" => "business_id_02",
              "business_name" => "Business Name 2",
          },
      ]
  },
  %{
      "item_id" => "id_02",
      "item_name" => "B",
      "description" => "B description",
      "item_category" => "some category",
      "businesses" => [
          %{
              "business_id" => "business_id_02",
              "business_name" => "Business Name 2",
          },
          %{
              "business_id" => "business_id_03",
              "business_name" => "Business Name 3",
          }
      ]
  }
]

问题分析 #

1. 缺失关联定义：ItemSchema中未定义与BusinessSchema的关联关系，preload: [:BusinessSchema]无效，因为Schema里没有对应关联字段。
2. 查询逻辑偏差：select中直接取item.business_ids只能得到字符串数组，无法拉取商家的详细数据。
3. Schema字段错误：ItemCategorySchema重复定义item_category_id字段，会导致数据映射异常。

解决方案 #

步骤1：修正Schema定义 #

修复ItemCategorySchema（移除重复字段）

schema "item_categories" do
  field(:item_category_id, :string)
  field(:category_name, :string)
end

为ItemSchema添加与BusinessSchema的关联

由于items表用business_ids数组存储关联商家ID，属于无中间表的多对多关联，可添加如下定义：

schema "items" do
  field(:item_id, :string)
  field(:item_category_id, :string)
  field(:user_id, :string)
  field(:business_ids, {:array, :string})

  field(:item_name, :string)
  field(:description, :string)

  # 通过business_ids数组匹配商家ID
  has_many :businesses, BusinessSchema,
    foreign_key: :business_id,
    references: :business_ids,
    where: fragment("? = ANY(?)", BusinessSchema.business_id, ^__MODULE__.business_ids)
end

步骤2：修改查询语句 #

方案1：用Join+聚合函数（单次查询，性能更优）

query =
  from(
    item in ItemSchema,
    join: item_cat in ItemCategorySchema,
    on: item.item_category_id == item_cat.item_category_id,
    left_join: business in BusinessSchema,
    on: business.business_id in item.business_ids,
    group_by: [item.item_id, item_cat.category_name],
    select: %{
      "item_id" => item.item_id,
      "item_name" => item.item_name,
      "description" => item.description,
      "item_category" => item_cat.category_name,
      "businesses" => fragment("array_agg(?)", map(business, [:business_id, :business_name]))
    }
  )

Repo.all(query)

方案2：用Preload（依赖Schema关联定义）

# 需确保ItemSchema已定义:businesses关联
query =
  from(
    item in ItemSchema,
    join: item_cat in ItemCategorySchema,
    on: item.item_category_id == item_cat.item_category_id,
    preload: [:businesses],
    select: %{
      "item_id" => item.item_id,
      "item_name" => item.item_name,
      "description" => item.description,
      "item_category" => item_cat.category_name,
      "businesses" => item.businesses
    }
  )

Repo.all(query)

补充说明 #

• 方案1通过数据库层面的left_join和array_agg聚合商家数据，减少查询次数，适合数据量较大的场景；
• 方案2依赖Ecto的关联预加载机制，逻辑更简洁，适合简单业务场景；
• 使用left_join可确保即使business_ids为空，也能返回空数组而非报错。

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