如何在Ecto Changeset中验证新增营业时间不与已有条目重叠

表结构说明 #

基于MySQL的business_hours表包含以下字段：

location_id (外键)
starting_time (浮点数，例如 17 = 下午5点, 17.5 = 下午5:30)
ending_time (浮点数)
day (整数，0 = 周一, 6 = 周日)

需求背景 #

需要创建Ecto Changeset函数，验证新增/更新的营业时间条目，不会与同地点、同一天的现有条目重叠。已知判断无重叠的逻辑为：

(a.starting_time > b.ending_time or a.ending_time < b.starting_time) and a.day == b.day and a.location_id == b.location_id

反过来，只要存在不满足该条件的现有记录，就说明时间范围重叠。

实现思路 #

1. 从Changeset中提取当前操作的location_id、day、starting_time、ending_time字段值
2. 查询同location_id和day下，是否存在与当前时间范围重叠的现有记录
3. 若存在重叠记录，为Changeset添加错误提示

代码实现 #

在对应的Ecto Schema模块（示例为MyApp.BusinessHours）中编写以下代码：

defmodule MyApp.BusinessHours do
  use Ecto.Schema
  import Ecto.Changeset
  alias Ecto.Query
  alias MyApp.Repo

  schema "business_hours" do
    field :starting_time, :float
    field :ending_time, :float
    field :day, :integer
    belongs_to :location, MyApp.Location
  end

  def changeset(business_hour, attrs) do
    business_hour
    |> cast(attrs, [:location_id, :starting_time, :ending_time, :day])
    |> validate_required([:location_id, :starting_time, :ending_time, :day])
    # 验证时间在合理范围内（0-24小时）
    |> validate_number(:starting_time, greater_than_or_equal_to: 0.0, less_than: 24.0)
    |> validate_number(:ending_time, greater_than_or_equal_to: 0.0, less_than: 24.0)
    # 先确保结束时间晚于开始时间
    |> validate_time_order()
    # 核心：验证无时间重叠
    |> validate_no_overlap()
  end

  # 辅助验证：结束时间必须晚于开始时间
  defp validate_time_order(changeset) do
    start_time = get_field(changeset, :starting_time)
    end_time = get_field(changeset, :ending_time)

    if start_time && end_time && end_time <= start_time do
      add_error(changeset, :ending_time, "必须晚于开始时间")
    else
      changeset
    end
  end

  # 验证与现有记录无重叠
  defp validate_no_overlap(changeset) do
    # 仅当基础验证通过后，才执行数据库查询
    if changeset.valid? do
      %{
        location_id: location_id,
        day: day,
        starting_time: current_start,
        ending_time: current_end
      } = get_fields(changeset, [:location_id, :day, :starting_time, :ending_time])

      # 构造查询：查找同地点、同一天且时间重叠的记录
      overlap_query =
        from bh in __MODULE__,
          where: bh.location_id == ^location_id,
          where: bh.day == ^day,
          # 重叠判断逻辑：现有记录的开始时间 < 当前结束时间，且现有记录的结束时间 > 当前开始时间
          where: bh.starting_time < ^current_end,
          where: bh.ending_time > ^current_start,
          # 更新操作时，排除当前记录本身
          where: bh.id != ^get_field(changeset, :id)

      # 检查是否存在重叠记录
      if Repo.exists?(overlap_query) do
        add_error(changeset, :starting_time, "该时间段与已有营业时间重叠")
      else
        changeset
      end
    else
      changeset
    end
  end
end

关键细节说明 #

• 重叠判断逻辑：直接用数据库查询验证是否存在重叠记录，逻辑等价于“现有记录的时间范围与当前时间范围有交集”，比反向判断更直观。
• 更新场景兼容：通过bh.id != ^get_field(changeset, :id)排除当前更新的记录，避免自己和自己比对。
• 性能优化：先完成基础字段验证（必填、时间范围、时间顺序），再执行数据库查询，减少无效的DB请求。

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