Insert a row into complicated table

Questions / Help
,
1

Hi guys, have been doing fine with elixir phoenix ecto as a beginner. Now i faced a problem where i don’t know how to go from there. So here is my problem that’s need your help:

I have 3 tables:

schema "invoices" do
    field :customer_name, :string
    field :shipping_address, :string
    has_many :items, WebApp.InvoiceItems.InvoiceItem
    belongs_to :customer_transaction, WebApp.CustomerTransactions.CustomerTransaction
  end

schema "invoice_items" do
    field :name, :string
    field :price, :decimal
    field :quantity, :decimal, default: 1
    belongs_to(:invoice, WebApp.Invoices.Invoice)
  end

schema "customer_transactions" do
    field(:amount, :decimal) # This amount will be the sum of all items (quantity * price)
    has_one(:invoice, WebApp.Invoices.Invoice)
  end

Summarized explanation:
1 invoice has many invoice_items.
1 invoice has 1-to-1 relationship with customer_transactions table (mandatory)
treat customer_transaction as an entry in bank statement. cannot edit or delete. it is final.
Whenever new invoice is created, a customer_transaction must be created together.

Here’s my problem:
When create invoice, customer_transaction needs to be created. But the transaction needs to record an “amount” which equals to total of all invoice_items (quantity * price of each item). I don’t know how to do that.

Currently am doing this (see the middle part CAPITAL LETTER with comment)

def create_invoice(attrs) do
    Ecto.Multi.new()
    |> Ecto.Multi.insert(:invoice, fn _ ->
      %Invoice{}
      |> Invoice.changeset(attrs)
    end)
    |> Ecto.Multi.insert(:customer_transaction, fn results ->
      %CustomerTransaction{
        amount: RESULTS.INVOICE.TOTAL, # this doesn't exist, but how do i get this total?
        transaction_type: :invoice
      }
    end)
    |> Repo.transaction()
    |> case do
      {:ok, results} ->
        broadcast({:ok, results.invoice}, :invoice_created)

      {:error, :invoice, error_changeset, _results_so_far} ->
        {:error, error_changeset}
    end
  end

how to i get the invoice’s total? subquery or anything simpler? I have no idea haha.
Thanks in advance.

Extra Note:
in case you wonder why i need a 1-to-1 relationship to customer_transaction that seem like extra or redundant, it is not, it will have relationship with payment_receipt, withdrawal or return or etc. just treat it like an entry in bank statement. That’s solely my idea, it might not be the proper way, of course if you have better idea, welcome to add.

2

Do your attrs contain the invoice_items you want to create? I believe they do.

So, can’t you sum their total?

   %CustomerTransaction{
        amount:  Enum.map(attrs["invoice_items"], fn item -> item.price) |> Enum.sum()
        transaction_type: :invoice
      }
3

You may be overthinking this - assuming Invoice.changeset constructs the InvoiceItems (via cast_assoc or similar), they should be right there in results.invoice.invoice_items.

If they are, you could add them up with something like:

result.invoice.invoice_items
|> Enum.map(fn item -> Decimal.mult(item.price, item.quantity) end)
|> Enum.reduce(Decimal.new(), &Decimal.add/2)