Inserting multiple tables related to each other

I’m learning to build web app using Phoenix Framework on my sideproject and have a scenario wherein 3 tables are to be inserted and the result of one insert operation will be used on another insert operation.

Given the following tables:

ChecklistTemplate
Name
ChecklistItem Default
Name None
Is Online Submit False
Checklists
ChecklistItem_ID
ChecklistTemplate_ID

I want to do:

  1. Insert single template to template table
  2. Batch insert multiple items
  3. Batch insert the resulting ids from items together with the template id in the first step

What I’ve tried:

attrs = %{items: [%{name: "National ID"}, %{name: "Postal ID"}], name: "Default"}
def create_checklist(attrs) do
    template_params = ChecklistTemplate.changeset(%ChecklistTemplate{}, attrs)

    case template_params.valid? do
      true ->
        {:ok, template} = template_params |> Repo.insert()

        items = Enum.map(attrs.items, fn item ->
          %ChecklistItem{}
          |> ChecklistItem.changeset(item)
          |> Repo.insert()
        end
        )

        result = Enum.map(items, fn {_, item} ->
          case item do
            %ChecklistItem{} ->
              %Checklist{item_id: item.id, template_id: template.id}
              |> Repo.insert()
            %Ecto.Changeset{} -> false
          end
        end )

        {:ok, result}

      false -> :error
    end
  end

What I want to know:

  1. Can I use transaction for the series of inserts, so it will not push through and rollback if invalid data was passed from frontend
  2. Can my code be shorten

If information provided is lacking, I will gladly add more information.
Any help would do.

Thank you very much.

Can I use transaction for the series of inserts, so it will not push through and rollback if invalid data was passed from frontend

sure, check https://hexdocs.pm/ecto/Ecto.Repo.html#c:transaction/2

Can my code be shorten

yes, try to rewrite it using Ecto.Multi module.

1 Like