Dealing with associations with Ecto

So once again Im stuck, I think that I have some problem with “database frameworks”, Im struggling really bad with Ecto, mainly with associations. Maybe the main issue here is my database design, who may be bad, even if this is the case, I need to be able to deal with it anyways.

My business logic is the following: I have a User(provided by Pow) which have many Expenses and Tags, and the Expense has many Tag. (Every User have his own Tags).

Im tryig to create a new Expense, I need to assoc a list of Tags and a User to it.
Params: %{“notes” => “I am a note!”, “tag_id” => [“2”, “3”], “value” => “12345”}
I have no clue on how to do it.

Pastebin links to the code:
Context and Assocs
Controller and Form

Thank you

I think Ecto should allow you to insert everything in one go and do some work for you. But if things get to complex or magical, remember that Repo.transaction is your friend and you can always do things more manually. Something like:

Repo.transaction(fn ->
  with {:ok, schema1} <- Schema1.create_changeset(params1) |> Repo.insert(),
       {:ok, schema2} <- Schema2.create_changeset(schema1, params2) |> Repo.insert(),
       # ...
    # Transaction returns {:ok, schemaN}
    # Transaction returns {:error, changeset}
    {:error, changeset} -> Repo.rollback(changeset)

or if you want to make things composable, you can use Multi:
|> Multi.insert(:schema1, Schema1.create_changeset(params1))
|>, fn repo, %{schema1: schema1} ->
  |> Schema2.create_changeset(params2)
  |> repo.insert()
# ...
|> Repo.transaction()
|> case do
  {:ok, %{schema1: schema1, ...}} -> {:ok, schemaN}
  {:error, _failed_operation, failed_value, _changes_so_far} -> {:error, failed_value}

At this level things translate into straightforward SQL inserts/updates. If you go this way, I’d use pattern matching to destructure the params in the controller:

def create(conn, %{"schema1" => %{"attr1" => ..., "schema2" => %{ ... } = params2} = params1}) do
  case Context.create_thing(params1, params2) do
    # ...

The reason to do so would be to support params with both string keys (this is how data is coming in via Phoenix) and atom keys (more convenient to use in tests or IEx).


I cant see how the associations are being done

I need to create a new Record and associate it with one existing User and one or many Tags(tag_id), only then, insert.

Im wondering if using Ecto.Query isnt a better idea instead of using those more “magic” functions

Params look like this:
%{"expense" => %{"note" => "Im a note", "tag_id" => ["4", "5"], "value" => "717891"}

I dont know how to deal with the multiple values from tag_id

Souldn it be like:

`%{"expense" => %{
     "note" => "Im a note", 
     "tags" => %{
          %{"tag" => %{"id" => "4"},
          %{"tag" => %{"id" => "5"},
     "value" => "717891"


schema "expenses" do
    field :value, :integer
    field :note, :string
    field :inserted_at, :naive_datetime
    has_many :tags, Finapp.Records.Tag
    belongs_to :user, Finapp.Users.User


<div class="field">
        <%= label f, :tag_id, "Categorias" %>
        <%= multiple_select f, :tag_id,, &{&, &}), class: "ui fluid search dropdown tag-menu" %>
        <%= error_tag f, :tag_id %>

Doesn’t look like your database structure allows it. You might like to draw out the tables on paper and enter some data in there to see how it looks.

You might want to read ecto docs on many to many associations.

So, the relation between Expense and Tag should be many to many? I thought about that, but, I would end up with an “expense_tag” table with expense_id, tag_id and a user_id, since every user have his own tags.

The structure would be like:
a user has many expenses and many tags,
an expense has many tags

I don’t think there would be a user_id in that table as you have user_id on the tags table.

User has user_id, Tags are associated with a user_id (not associated with an expense so no expense_id column), Expenses have multiple tags, so you need an expense_tags table which is just expense_id and tag_id, no?


Well, I did it, still cant figure out how to make this work with multiple_select. Its really frustrating, it is so trivial with plain sql, Im struggling too much with Ecto, I think Im done…

Thank you for your attention