Ecto查询嵌套关联时如何实现每个Device仅返回最新1条DeviceLocation

问题原因 #

之所以给location_query加limit: 1会仅返回全局1条记录，是因为Ecto对一对多关联的预加载默认使用in查询：先拿到所有设备的id列表，再执行SELECT * FROM device_locations WHERE device_id IN (?, ?, ?) ... LIMIT 1，这个limit是全局生效的，不会按device_id分组限制。

解决方案 #

方案1：使用PostgreSQL DISTINCT ON（最简洁，适配PG场景）

PostgreSQL的DISTINCT ON语法可以按指定字段分组，每组仅返回第一条记录，配合排序规则即可拿到每个设备的最新位置：

def list_pdevices_locations(user, limit \\ 0) do
  # 每个device_id仅保留最新的1条位置记录
  latest_location_query = 
    from dl in DeviceLocation,
      distinct: dl.device_id,
      order_by: [desc: dl.device_id, desc: dl.inserted_at]

  query =
    from d in ProvisionedDevice,
      preload: [device_info: [locations: ^latest_location_query]],
      limit: ^limit

  result = 
    if user.is_admin do
      Repo.all(query)
    else
      Repo.all(from(d in query, where: d.user_id == ^user.id))
    end

  # 可选：把单元素的locations列表转为单个位置对象，更符合使用习惯
  Enum.map(result, fn pdevice ->
    update_in(pdevice.device_info.locations, fn 
      [] -> nil
      [latest] -> latest
    end)
  end)
end

注意：PostgreSQL要求DISTINCT ON的字段必须是ORDER BY的第一个字段，所以排序规则里要先写dl.device_id再写时间倒序。

方案2：定义可复用的has_one关联

如果多场景都需要获取设备最新位置，可以直接在Device模型中定义专属关联，后续预加载直接使用即可：

# Device 模型文件
has_one :latest_location, DeviceLocation,
  query: [distinct: :device_id, order_by: [desc: :device_id, desc: :inserted_at]]

业务查询简化为：

def list_pdevices_locations(user, limit \\ 0) do
  query =
    from d in ProvisionedDevice,
      preload: [device_info: :latest_location],
      limit: ^limit

  if user.is_admin do
    Repo.all(query)
  else
    Repo.all(from(d in query, where: d.user_id == ^user.id))
  end
end

使用这个方案查询后，直接通过device.latest_location就能拿到最新位置，不需要额外处理列表。

方案3：兼容非PG数据库的窗口函数方案

如果不使用PostgreSQL，可以用通用窗口函数row_number()实现分组取第一条：

latest_location_query =
  from dl in DeviceLocation,
    select: %{dl | rn: row_number() |> over(partition_by: dl.device_id, order_by: [desc: dl.inserted_at])},
    where: dl.rn == 1

所有支持窗口函数的数据库都可以使用该方案。

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