How to model and enforce: "A table has zero or one of multiple specification associations"

Questions / Help
, , , ,
1

I want to model the following relationship:

A location has zero or one of multiple (currently 7) specifications (each specification is a has_one association)

The goals:

  • Strict DB consistency. Unable to set more than one specification.
  • Easily maintainable and extensible
  • Efficient query

I have locations:

defmodule Schnitzel.Repo.Migrations.CreateLocations do
  use Ecto.Migration

  def change do
    create table(:locations) do
      add :world_id, references(:worlds, on_delete: :delete_all), null: false
      add :timeslot, :integer, null: false
      add :visibility, :float, null: false, default: 0
      add :items, :string, null: false, default: "[]"
    end
  end
end

Those locations have zero or one of multiple specifications (Here building, vendor and object; I removed some for clarity):

defmodule Schnitzel.Repo.Schema.Location do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.World
  alias Schnitzel.Repo.Types.TupleMap
  alias Schnitzel.Repo.Schema.LocationOwner

  schema "locations" do
    belongs_to :world, World
    field :timeslot, :integer
    field :visibility, :float, default: 0.0
    field :items, TupleMap, default: %{}

    has_one :location_owner, LocationOwner

    has_one :building, through: [:location_owner, :building]
    has_one :vendor, through: [:location_owner, :vendor]
    has_one :object, through: [:location_owner, :object]
  end

  @doc false
  def changeset(location, attrs) do
    location
    |> cast(attrs, [:world_id, :timeslot, :visibility, :items])
    |> validate_required([:world_id, :timeslot, :visibility, :items])
    |> foreign_key_constraint(:world_id)
  end
end

I have two tables to enforce the relationship rules:

defmodule Schnitzel.Repo.Migrations.CreateLocationOwners do
  use Ecto.Migration

  def change do
    create table(:location_owners) do
      add :location_id, references(:locations, on_delete: :delete_all), null: false
      add :location_owner_type_id, references(:location_owner_types, on_delete: :restrict), null: false
      add :location_owner_id, :bigint, null: false
    end

    create unique_index(:location_owners, [:location_id])
    create unique_index(:location_owners, [:location_owner_type_id, :location_owner_id])
  end
end

defmodule Schnitzel.Repo.Schema.LocationOwner do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.Location
  alias Schnitzel.Repo.Schema.LocationOwnerType

  schema "location_owners" do
    belongs_to :location, Location
    belongs_to :location_owner_type, LocationOwnerType
    field :location_owner_id, :id
  end

  def changeset(location_owner, attrs) do
    location_owner
    |> cast(attrs, [:location_id, :location_owner_type_id, :location_owner_id])
    |> validate_required([:location_id, :location_owner_type_id, :location_owner_id])
    |> foreign_key_constraint(:location_id)
    |> foreign_key_constraint(:location_owner_type_id)
    |> unique_constraint(:location_id)
    |> unique_constraint([:location_owner_type_id, :location_owner_id])
  end
end

defmodule Schnitzel.Repo.Migrations.CreateLocationOwnerTypes do
  use Ecto.Migration

  def change do
    create table(:location_owner_types) do
      add :name, :string, null: false # the specification name (e.g. "building", "object", "vendor")
    end

    create unique_index(:location_owner_types, [:name])
  end
end

defmodule Schnitzel.Repo.Schema.LocationOwnerType do
  use Ecto.Schema

  import Ecto.Changeset

  schema "location_owner_types" do
    field :name, :string
  end

  def changeset(location_owner_type, attrs) do
    location_owner_type
    |> cast(attrs, [:name])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end
end

The specifications look like this:

defmodule Schnitzel.Repo.Migrations.CreateVendors do
  use Ecto.Migration

  def change do
    create table(:vendors) do
      add :location_id, references(:locations, on_delete: :delete_all), null: false
      add :items, :string, null: false, default: "[]"
    end
  end
end

defmodule Schnitzel.Repo.Schema.Vendor do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.Location
  alias Schnitzel.Repo.Types.TupleMap

  schema "vendors" do
    belongs_to :location, Location
    field :items, TupleMap, default: %{}
  end

  @doc false
  def changeset(vendor, attrs) do
    vendor
    |> cast(attrs, [:location_id, :items])
    |> validate_required([:location_id, :items])
    |> foreign_key_constraint(:location_id)
  end
end

My query is unfinished but it works:

defp location_query() do
  from l in Location,
    left_join: lo in assoc(l, :location_owner),

    left_join: b in Building,
    on: lo.location_owner_id == b.id and lo.location_owner_type_id == 1,
    left_join: v in Vendor,
    on: lo.location_owner_id == s.id and lo.location_owner_type_id == 6,
    left_join: o in Object,
    on: lo.location_owner_id == o.id and lo.location_owner_type_id == 5,

    preload: [building: b, vendor: v, object: o]
end

I would like to create a using macro that creates the join expression for each specification and fetches the location_owner_type_id during compilation, if that is possible. Like:

defmodule Schnitzel.Data.Building do
  use Schnitzel.LocationOwner, scheme: Schnitzel.Repo.Scheme.Building, type: :building # the type is used to look-up the location_owner_type_id
end

So i can do something like:

defp location_query() do
  from l in Location,
    left_join: lo in assoc(l, :location_owner),

    Schnitzel.Data.Building.left_join(lo)
    Schnitzel.Data.Vendor.left_join(lo)
    Schnitzel.Data.Object.left_join(lo)
end

I have created:

defmodule Schnitzel.LocationOwner do
  @doc false
  defmacro __using__(opts) do
    schema = Keyword.fetch!(opts, :schema)
    type = Keyword.fetch!(opts, :type)

    quote location: :keep do
      alias Ecto.Multi
      alias Schnitzel.Repo
      alias Schnitzel.Repo.Schema.LocationOwner
      alias Schnitzel.Repo.Schema.LocationOwnerType

      def create(attrs \\ %{}) do
        # TODO bake at compile time:
        location_owner_type = Repo.get_by!(LocationOwnerType, name: unquote(Atom.to_string(type)))

        Multi.new()
        |> Multi.insert(unquote(type), unquote(schema).changeset(%unquote(schema){}, attrs))
        |> Multi.run(:location_owner, create_owner(location_owner_type.id))
        |> Repo.transact()
      end

      defp create_owner(location_owner_type_id) do
        fn repo, %{unquote(type) => location_owner} ->
          %LocationOwner{}
          |> LocationOwner.changeset(%{location_id: location_owner.location_id, location_owner_type_id: location_owner_type_id, location_owner_id: location_owner.id})
          |> repo.insert()
        end
      end
    end
  end
end

But i dont know how to do the query expression macro.

Basically, what i am asking is:

  • How do i compose an easily maintainable and extensible query?
  • Is all this (the most) efficient (best) way to solve my problem?
  • What if i had 100 specifications?

If there is something else, please do tell.

Thank you very much for reading,

Til

1 Like
2

I realized i don’t know why this still works. I removed the correspondig has_one :building from Schnitzel.Repo.Schema.LocationOwner a while ago. When i do has_one :building, Schnitzel.Repo.Schema.Building in Schnitzel.Repo.Schema.Location instead, it seems to work the same.

1 Like
3

It’s definitely going to take some serious hackery to run queries against Repo at compile-time; IIRC the app isn’t normally booted at that time.

I’m not sure that LocationOwnerType should even be a database table, since adding an additional type of location takes more than just adding a row. Consider something like Ecto.Enum instead, which would keep the mapping entirely in code, and readily accessible during compilation.

Final thought: what purpose is LocationOwner serving? If there’s always exactly one (or zero), what’s the benefit versus keeping location_id and location_owner_type_id directly on Location?

2 Likes
4

I use LocationOwner to enforce the only-one-specification rule. That table is all i need, no extra logic. But i dropped LocationOwnerType in favor of Ecto.Enum, simplified a few things and am happy now.

Here is the result:

defmodule Schnitzel.Repo.Migrations.CreateLocations do
  use Ecto.Migration

  def change do
    create table(:locations) do
      add :world_id, references(:worlds, on_delete: :delete_all), null: false
      add :timeslot, :integer, null: false
      add :visibility, :float, null: false, default: 0
      add :items, :string, null: false, default: "[]"
    end
  end
end

defmodule Schnitzel.Repo.Schema.Location do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.World
  alias Schnitzel.Repo.Types.TupleMap
  alias Schnitzel.Repo.Schema.LocationOwner
  alias Schnitzel.Repo.Schema.Building
  alias Schnitzel.Repo.Schema.Object
  alias Schnitzel.Repo.Schema.Vendor

  schema "locations" do
    belongs_to :world, World
    field :timeslot, :integer
    field :visibility, :float, default: 0.0
    field :items, TupleMap, default: %{}
    has_one :location_owner, LocationOwner
    has_one :building, Building
    has_one :object, Object
    has_one :vendor, Vendor
  end

  @doc false
  def changeset(location, attrs) do
    location
    |> cast(attrs, [:world_id, :timeslot, :visibility, :items])
    |> validate_required([:world_id, :timeslot, :visibility, :items])
    |> foreign_key_constraint(:world_id)
  end
end

defmodule Schnitzel.Repo.Migrations.CreateLocationOwners do
  use Ecto.Migration

  def change do
    execute("CREATE TYPE location_owner AS ENUM ('building', 'object', 'vendor');", "DROP TYPE location_owner")

    create table(:location_owners) do
      add :location_id, references(:locations, on_delete: :delete_all), null: false
      add :owner_type, :location_owner, null: false
    end

    create unique_index(:location_owners, [:location_id])
  end
end

defmodule Schnitzel.Repo.Schema.LocationOwner do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.Location

  schema "location_owners" do
    belongs_to :location, Location
    field :owner_type, Ecto.Enum, values: [:building, :object, :vendor]
  end

  def changeset(location_owner, attrs) do
    location_owner
    |> cast(attrs, [:location_id, :owner_type])
    |> validate_required([:location_id, :owner_type])
    |> foreign_key_constraint(:location_id)
    |> unique_constraint(:location_id)
  end
end

defmodule Schnitzel.Repo.Migrations.CreateVendors do
  use Ecto.Migration

  def change do
    create table(:vendors) do
      add :location_id, references(:locations, on_delete: :delete_all), null: false
      add :location_owner_id, references(:location_owners, on_delete: :delete_all), null: false
      add :items, :string, null: false, default: "[]"
    end
  end
end

defmodule Schnitzel.Repo.Schema.Vendor do
  use Ecto.Schema

  import Ecto.Changeset

  alias Schnitzel.Repo.Schema.Location
  alias Schnitzel.Repo.Schema.LocationOwner
  alias Schnitzel.Repo.Types.TupleMap

  schema "vendors" do
    belongs_to :location, Location
    belongs_to :location_owner, LocationOwner
    field :items, TupleMap, default: %{}
  end

  @doc false
  def changeset(vendor, attrs) do
    vendor
    |> cast(attrs, [:location_id, :location_owner_id, :items])
    |> validate_required([:location_id, :location_owner_id, :items])
    |> foreign_key_constraint(:location_id)
    |> foreign_key_constraint(:location_owner_id)
  end
end

defp location_query() do
  from l in Location,
    left_join: lo in assoc(l, :location_owner),
    left_join: b in Building,
    on: b.location_owner_id == lo.id and lo.owner_type == :building,
    left_join: o in Object,
    on: o.location_owner_id == lo.id and lo.owner_type == :object,
    left_join: v in Vendor,
    on: v.location_owner_id == lo.id and lo.owner_type == :vendor,
    preload: [building: b, object: o, vendor: v]
end

Thank you!