Handling supertype subtype constraints in Ecto

Hi all, I’ve encountered a situation I’m having trouble implementing elegantly using Ecto. I have three tables in my schema which comprise one supertype and two subtypes. The supertype is a Plan which can be either a Match Plan or a Fixed Plan, not both.

Supertype: Plan

schema "plan" do
  belongs_to: :frequency, Frequency
  has_one: :match_plan, MatchPlan
  has_one: :fixed_plan, FixedPlan

Subtypes: MatchPlan, FixedPlan

# Unique index on :percentage and :limit (DATABASE CONSTRAINT)
schema "match_plan" do
  belongs_to :plan, Plan
  :percentage, :decimal
  :limit, :decimal

# Unique index on :amount (DATABASE CONSTRAINT)
schema "fixed_plan" do
  belongs_to :plan, Plan
  :amount, :decimal

My challenge is creating a plan that I pair with a user to create a UserPlan. But I need to accommodate the constraint on the subtypes when creating a plan. I don’t see how this can be done without two Ecto.Multis:

# First Multi
def create_user_plan(user, params) do
  |> Multi.run(:plan, &maybe_insert_plan(&1, params))
  |> Multi.run(:user_plan, &insert_user_plan(&1, user))
  |> Repo.transaction() 

def maybe_insert_plan(params) do
  case insert_plan(params) do
    {:ok, %{plan: plan}} ->
      {:ok, plan}
    {:error, _, _, _} ->
      {:ok, lookup_plan(params)}

# Second Multi
def insert_plan(params) do
  |> Multi.run(:frequency, &get_frequency(&1, params))
  |> Muti.run(:plan, &insert_plan/1)
  |> Multi.run(:plan_subtype, &insert_plan_subtype(&1, params)) # This is the function that will raise the unique constraint error
  |> Repo.transaction()

Is there are more idiomatic way to handle situations like these?

1 Like