Loading data from different child tables in one query

Questions / Help
1

I have a parent entity and children. The parent entity has a subject_type field - it is responsible for which table the child entity is from, that is, this field stores the name of the child table. That is, child entities can be from different tables. I want to get a list of parent entities with loaded child entities in one request. How can this be implemented?

defmodule MyApp.Parent do
  use Ecto.Schema
  import Ecto.Changeset

  schema "parents" do
    field :name, :string
    field :subject_type, :string
    field :subject_id, :integer
    timestamps()
  end

  def changeset(parent, attrs) do
    parent
    |> cast(attrs, [:name, :subject_type, :subject_id])
    |> validate_required([:name, :subject_type, :subject_id])
  end
end

defmodule MyApp.ChildType1 do
  use Ecto.Schema
  import Ecto.Changeset

  schema "child_type1" do
    field :description, :string
    timestamps()
  end

  def changeset(child, attrs) do
    child
    |> cast(attrs, [:description])
    |> validate_required([:description])
  end
end

defmodule MyApp.ChildType2 do
  use Ecto.Schema
  import Ecto.Changeset

  schema "child_type2" do
    field :details, :string
    timestamps()
  end

  def changeset(child, attrs) do
    child
    |> cast(attrs, [:details])
    |> validate_required([:details])
  end
end
2

The Ecto.Schema documentation has a section on Polymorphic associations which would be worth a read.

If you only need a couple of child types, such as your example, the recommended approach is to define multiple fields, one for each child type:

schema "parents" do
    field :name, :string
    has_many :type1_children, MyApp.ChildType1
    has_many :type2_children, MyApp.ChildType2
    timestamps()
end
1 Like
3

I need to get the child type in one field and not in different ones, this is the problem.

4

How would you do it in SQL?

5

Postgres doesn’t have great support for modeling data in this way.

I would have just one child table that contains all of the common fields of child types. Then I would have a json field that contains the data that’s specific to that type.

If that doesn’t work for you, another option is to have custom insert rules that maintain the integrity across tables manually. I’m not sure how this works with ecto however