Creating entity with associations, problems with querying in Ecto and putting associations

mbski · November 11, 2022, 8:21pm

Hi,

This time I stuck in quite complex problem for me which is absolutely simple when I write on paper step by step what should I do, but I dont know why Ecto doesnt cooperate with me as I expect

Long story short - while creating book ecto should check if authors/categories exists. If they are - new book should be associated with them, if not - they should be inserted into db and then associated with book.

I have book controller and I want to create a book, which has simple informations and is associated with Author and Category. Relations between Book-Author/Category is many_to_many, and I have created also third table that keeps relations between them.

Book schema:

  schema "books" do
    field :title, :string
    field :description, :string
    field :published, :integer
    field :isbn, :string
    field :is_available, :boolean, default: true
    field :to_borrow, :boolean, default: true
    field :to_sale, :boolean, default: false
    field :price, :decimal

    belongs_to :user, Bookshare.Accounts.User, foreign_key: :user_id
    many_to_many :categories, Bookshare.Books.Category, join_through: "books_categories", on_replace: :delete
    many_to_many :authors, Bookshare.Books.Author, join_through: "books_authors", on_replace: :delete

    timestamps()
  end

  @doc false
  def changeset(book, attrs) do
    book
    |> cast(attrs, [:title, :description, :published, :isbn, :is_available, :to_borrow, :to_sale, :price])
    #|> cast_assoc(:categories)
    |> cast_assoc(:authors)
    |> validate_required([:title, :description, :published, :isbn])
    |> unique_constraint(:isbn)
  end

Author schema (Category looks the same):

  schema "authors" do
    field :name, :string

    many_to_many :books, Bookshare.Books.Book, join_through: "books_authors"

    timestamps()
  end

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

  end

Relation migration table:

  def change do
    create table(:books_authors, primary_key: false) do
      add :book_id, references(:books), primary_key: true
      add :author_id, references(:authors), primary_key: true
    end

    create unique_index(:books_authors, [:book_id, :author_id])
  end

I found something like that on github and used it as a base with my fields. What is strange is book doesnt have any reference to authors/categories, but maybe Elixir is smarter than most of programming languages and doesnt need it.

Book controller:

  def create(conn, %{"book" => book_params}) do
    user = conn.assigns.current_user

    with {:ok, %Book{} = _book} <- Books.create_book(user, book_params) do
      conn
      |> put_status(:created)
      |> json(%{message: "created"})
    end
  end

Pretty simple stuff. User needed to get user_id. In params I put all informations about book with authors/categories.

Books.create_book:

  def create_book(user, %{"authors" => authors, "categories" => categories} = attrs) do
    %Book{}
    |> Book.changeset(Map.drop(attrs, ["authors", "categories"]))
    |> Ecto.Changeset.put_assoc(:user, user)
    |> load_authors_assoc(attrs)
    #|> load_categories_assoc(attrs)
    |> Repo.insert()
  end

load_authors_assoc(attrs):

  defp load_authors_assoc(book, %{"authors" => authors} = attrs) do
    #authors = Repo.all(from a in Author, where: a.name == ^attrs["authors"])
    if Repo.exists?(from a in Author, where: a.name == ^attrs["authors"]) do
      book
      |> Ecto.Changeset.change()
      |> Ecto.Changeset.put_assoc(:authors, authors)
    else
      {:ok, %Author{} = authors} = Repo.insert(%Author{name: authors})
        book
        |> Ecto.Changeset.change()
        |> Ecto.Changeset.put_assoc(:authors, authors)
    end
  end

This is place where I struggle a lot. Almost everytime I get info that I put “invalid name” and 422 error.

{
    "errors": {
        "authors": [
            "is invalid"
        ]
    }
}

And in terminal:

[info] POST /api/books/create
[debug] Processing with BookshareWeb.BookController.create/2
  Parameters: %{"book" => %{"authors" => "merry", "categories" => "horror", "description" => "test", "isbn" => "47r5714-31d8230df1-3e2322", "published" => 2010, "title" => "KSIOZNKA"}}
  Pipelines: [:api]
[debug] QUERY OK source="users_tokens" db=3.0ms queue=1.0ms idle=1323.5ms
SELECT u1."id", u1."email", u1."hash_password", u1."is_confirmed", u1."inserted_at", u1."updated_at" FROM "users_tokens" AS u0 INNER JOIN "users" AS u1 ON u1."id" = u0."user_id" WHERE ((u0."token" = $1) AND (u0."context" = $2)) AND (u0."inserted_at" > $3::timestamp + (-(30)::numeric * interval '1 day')) ["WxhnNGufov6I9J3A5eJ4BMuW6yQHu_LCU1J1645j-QY", "session", ~U[2022-11-11 20:05:41.933551Z]]
↳ BookshareWeb.Auth.fetch_current_user/2, at: lib/bookshare_web/auth.ex:30
[debug] QUERY OK source="authors" db=0.6ms queue=1.4ms idle=1352.5ms
SELECT TRUE FROM "authors" AS a0 WHERE (a0."name" = $1) LIMIT 1 ["merry"]
↳ Bookshare.Books.load_authors_assoc/2, at: lib/bookshare/books.ex:137
[info] Sent 422 in 92ms

After playing with function I found out that sometimes it works partially.
1st: If I have author in db that part of function works. It creates book and associate it with author in db, but without author in db it just creates book without author and association:

  defp load_authors_assoc(book, %{"authors" => authors} = attrs) do
    authors = Repo.all(from a in Author, where: a.name == ^attrs["authors"])
       book
       |> Ecto.Changeset.change()
       |> Ecto.Changeset.put_assoc(:authors, authors)
  end

2nd: I tried to you old fashioned If statement to check if Author is in db or not, and then make decision:

  defp load_authors_assoc(book, %{"authors" => authors} = attrs) do
    if Repo.all(from a in Author, where: a.name == ^attrs["authors"]) != nil do
       book
       |> Ecto.Changeset.change()
       |> Ecto.Changeset.put_assoc(:authors, authors)
    else
        {:ok, %Author{} = new_authors} = Repo.insert(%Author{name: authors})
        book
        |> Ecto.Changeset.change()
        |> Ecto.Changeset.put_assoc(:authors, new_authors)
    end
end

And here again I get 422 It does not matter if I write author existing in db or not. I tried to use Repo.get_by and Repo.one but they never returns existing Author from db. I have been fighting with this for many hours and I do not have any idea today how to solve this problem.

al2o3cr · November 11, 2022, 9:00pm

mbski:

2nd: I tried to you old fashioned If statement to check if Author is in db or not, and then make decision:
  defp load_authors_assoc(book, %{"authors" => authors} = attrs) do
    if Repo.all(from a in Author, where: a.name == ^attrs["authors"]) != nil do
       book
       |> Ecto.Changeset.change()
       |> Ecto.Changeset.put_assoc(:authors, authors)
    else
        {:ok, %Author{} = new_authors} = Repo.insert(%Author{name: authors})
        book
        |> Ecto.Changeset.change()
        |> Ecto.Changeset.put_assoc(:authors, new_authors)
    end
end
And here again I get 422 It does not matter if I write author existing in db or not. I tried to use Repo.get_by and Repo.one but they never returns existing Author from db. I have been fighting with this for many hours and I do not have any idea today how to solve this problem.

The root of your problem is that you aren’t passing the right type to put_assoc(:authors) - it expects a list of either maps, %Author{} structs, or Ecto.Changesets containing Authors.

In the code quoted above, put_assoc is handed either:

a single string from attrs["authors"]
a single %Author{} struct from Repo.insert

Neither of those are of an expected type, so the changeset gets marked as invalid.

mbski · November 12, 2022, 5:57am

God, thank you for really fast response. Now I see the difference and I didnt realize that Repo.insert doesnt return whole struct needed to make put_assoc works. I relied too much on my previous put_assoc and didnt catch that there is whole user struct not just its id:
|> Ecto.Changeset.put_assoc(:user, user)

Actually I refactored function to:

    if Repo.exists?(from a in Author, where: a.name == ^attrs["authors"]) do
      authors = Repo.all(from a in Author, where: a.name == ^authors)
      book
      |> Ecto.Changeset.change()
      |> Ecto.Changeset.put_assoc(:authors, authors)
    else
      {:ok, %Author{} = authors} = Repo.insert(%Author{name: authors}, returning: true)
      authors = Repo.all(from a in Author, where: a.name == ^authors.name)
      book
      |> Ecto.Changeset.change()
      |> Ecto.Changeset.put_assoc(:authors, authors)
    end

It looks ugly, and too repepitive but works well. I will play with it

If you check my response accidentally - I think this function works well with one author. If I need to pass few of them, should I firstly split them into array/list and then do whole process of creating instances/associations?

Anyway big thanks, everything seems to be so obvious after hours of struggling

dimitarvp · November 12, 2022, 9:56am

That is a terrible way to link records, what’s wrong with the good old author_id being present in every books record?

mbski · November 12, 2022, 10:16am

Actually, as I showed in first post, there is no Author_id in Books (book can have many authors, should I store id’s as an array of ids then?), and there is no book_id in Author, there are third table having relations of both. This is why creating this was harder for me. I saw few times many_to_many relationship created like that what suprised me, for me its strange that entity doesnt have related id to other table.

Whats more, it gets difficult if I want to have more that one Author/Category to save for the book.

dimitarvp · November 12, 2022, 11:05am

IMO many_to_many is the way to go then, what’s the difficulty there? Not sure I understand.

mbski · November 12, 2022, 2:40pm

This is exactly what I do - many_to_many relationship. I found how to shorten whole load_author_assoc function. For now everything works well:

  defp load_authors_assoc(book, %{"authors" => authors} = attrs) do
    # if Repo.exists?(from a in Author, where: a.name == ^attrs["authors"]) do
    #   authors = Repo.all(from a in Author, where: a.name == ^authors)
    #   book
    #   |> Ecto.Changeset.change()
    #   |> Ecto.Changeset.put_assoc(:authors, authors)
    # else
    #   {:ok, %Author{} = authors} = Repo.insert(%Author{name: authors}, returning: true)
    #   authors = Repo.all(from a in Author, where: a.id == ^authors.id)
    #   book
    #   |> Ecto.Changeset.change()
    #   |> Ecto.Changeset.put_assoc(:authors, authors)
    # end
    Repo.insert_all("authors", [[name: authors, inserted_at: DateTime.utc_now(), updated_at: DateTime.utc_now()]], on_conflict: :nothing)
    authors = Repo.all(from a in Author, where: a.name == ^authors)
    book
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.put_assoc(:authors, authors)
  end

I commented out part I tried to refactor just to see the difference. I dont know if it is good practice to use insert_all in similar cases but I just read it in PragProg book about Ecto

al2o3cr · November 12, 2022, 5:21pm

put_assoc expects an input that matches the association:

for an association that can only have one thing on the other side (belongs_to, has_one) it expects a single struct
for an association that can have many things on the other side (has_many, many_to_many) it expects a list of structs

One general tip: naming can help prevent list-vs-struct confusion, but only if it’s used consistently. For instance, this code:

{:ok, %Author{} = authors} = Repo.insert(%Author{name: authors})

should jump out as “odd” because it assigns a single struct from Repo.insert to a variable with a plural name (authors). Not following that convention makes a line like put_assoc(:authors, authors) look correct even though it’s got a mismatched type.