Can't query associated table

Question

I have a simple todo / author model where todo has a author_id field.

The models are defined as follows:

defmodule TodoElixir.User.Author do
  use Ecto.Schema
  import Ecto.Changeset

  schema "authors" do
    field :email, :string
    field :name, :string
    field :password, :string, virtual: true   
    field :hash, :string
    has_many :todos, Main.Todo

    timestamps()
  end

Here I get a

warning: invalid association todo in schema TodoElixir.User.Author: associated schema Main.Todo does not exist

And the todo model:

defmodule TodoElixir.Main.Todo do
  use Ecto.Schema
  import Ecto.Changeset

  schema "todos" do
    field :date, :date
    field :description, :string
    field :title, :string
    belongs_to :author, User.Author

    timestamps()
  end

I also have a migration for each:

defmodule TodoElixir.Repo.Migrations.CreateAuthors do
  use Ecto.Migration

  def change do
    create table(:authors) do
      add :name, :string
      add :email, :string
      add :hash, :string
      has_many :todos, Main.Todo

      timestamps()
    end

  end
end

defmodule TodoElixir.Repo.Migrations.CreateTodos do
  use Ecto.Migration

  def change do
    create table(:todos) do
      add :title, :string
      add :description, :string
      add :date, :date
      add :author_id, references(:authors)

      timestamps()
    end

  end
end

If I remove has_many :todos, Main.Todo from the module, it compiles and I can query http://localhost:4000/api/todos but the author field is not set.

I've tried using preload and assoc but following https://elixirschool.com/en/lessons/ecto/associations/ the association should be automatic...

In the todo controller I have:

  def index(conn, _params) do
    todos = Main.list_todos()
    render(conn, "index.json", todos: todos)
  end

and list_todos =

  def list_todos do
    Repo.all(Todo)
  end

EDIT:

In the controller I put:

  def index(conn, _params) do
    todos = Repo.all(Todo) |> Repo.preload(:author)
    render(conn, "index.json", todos: todos)
  end

I see the query in the console:

[debug] Processing with TodoElixirWeb.TodoController.index/2
Parameters: %{} Pipelines: [:api] [debug] QUERY OK source="todos" db=6.3ms decode=1.7ms queue=0.8ms SELECT t0."id", t0."date", t0."description", t0."title", t0."author_id", t0."inserted_at", t0."updated_at" FROM "todos" AS t0 [] [debug] QUERY OK source="authors" db=0.6ms queue=1.0ms SELECT a0."id", a0."email", a0."name", a0."hash", a0."inserted_at", a0."updated_at", a0."id" FROM "authors" AS a0 WHERE (a0."id" = $1)

Which looks good to me, but the JSON result:

{"data":[{"date":null,"description":"we need to do this","id":1,"title":"My first todo"}]}

Should I tell Elixir to add the associations in the JSON response as well? How?

I tried has_many :todos, TodoElixir.Main.Todo which compiles with no warning, but I still can't see the association... — gyc

Pau Pau · Accepted Answer · 2019-12-12T04:49:42

Based from the requirements needed

I have simple todo / author model where todo has an author_id field that needs to parse as JSON.

First have a migration

defmodule TodoElixir.Repo.Migrations.CreateAuthorsTodos do
  use Ecto.Migration

  def change do
    # create authors
    create table(:authors) do
      add :name, :string
      add :email, :string
      add :hash, :string


      timestamps()
    end

    flush() # this one will execute migration commands above [see Ecto.Migration flush/0][1] 

   # create todos
   create table(:todos) do
      add :title, :string
      add :description, :string
      add :date, :date
      add :author_id, references(:authors)

      timestamps()
    end
  end
end

Set Tables and relationships for each tables. You can view Ecto Schema and see different functions to set them. In this case will be using has_many and belongs_to

defmodule TodoElixir.User.Author do
  use Ecto.Schema
  import Ecto.Changeset

  schema "authors" do
    field :email, :string
    field :name, :string
    field :password, :string, virtual: true   
    field :hash, :string
    has_many :todos, TodoElixir.Main.Todo 

    timestamps()
  end
end

defmodule TodoElixir.User.Todo do
  use Ecto.Schema
  import Ecto.Changeset

  schema "todos" do
    field :date, :date
    field :description, :string
    field :title, :string
    belongs_to :author, TodoElixir.User.Author # -> this will be used upon preload in your controller

    timestamps()
  end
end

In your controller, to preload you can do it like this
first alias your resources: Author, Todo, and your Repo
then create function to call all TODO preloading AUTHOR.

  alias TodoElixir.User.{Author, Todo} # -> your tables
  alias TodoElixir.Repo # -> call your repo

  def index(conn, _params) do
    todos = list_todos()
    render(conn, "index.json", todos: todos)
  end

 defp list_todos() do
   Todo
   |> Repo.all()
   |> Repo.preload(:author)
 end

Now to render json associated with author, let's go back to TODO and AUTHOR schema
to load them as JSON, you can use either JASON or POISON.
for this one we will use JASON

# in your endpoint.ex
# set up Jason using this one.

plug Plug.Parsers,
  parsers: [:urlencoded, :multipart, :json],
  pass: ["*/*"],
  json_decoder: Jason


# in your TODO and AUTHOR schemas derived the fields that you need in each tables.

defmodule TodoElixir.User.Todo do
  use Ecto.Schema
  import Ecto.Changeset

  # this is the key parsing them
  @derive Jason.Encoder
  defstruct %{
       :date,
       :description,
       :title,
       :author # -> This will show author. take note, if you do not preload author via TODO, this will cause error
  }

  schema "todos" do
    field :date, :date
    field :description, :string
    field :title, :string
    belongs_to :author, TodoElixir.User.Author

    timestamps()
  end
end

# since we call AUTHOR inside TODO, we also need to derived fields from Author. # Otherwise it will cause error.

defmodule TodoElixir.User.Author do
  use Ecto.Schema
  import Ecto.Changeset

  # you can also call fields that you want to parse.
  @derive Jason.Encoder
  defstruct %{
      :email,
      :name,
      :id
  }

  schema "authors" do
    field :email, :string
    field :name, :string
    field :password, :string, virtual: true   
    field :hash, :string
    has_many :todos, TodoElixir.Main.Todo 

    timestamps()
  end
end

Now in your VIEW, you can set up like this one

   def render("index.json", %{todos: todos}) do 
    todos
   end

Additional notes: if you don't want to derive fields in your schema and still want to parse them as json, you can do it like this.

# in your CONTROLLER, 

 alias TodoElixir.User.{Author, Todo} # -> your tables
 alias TodoElixir.Repo # -> call your repo

 def index(conn, _params) do
    todos = list_todos()
    render(conn, "index.json", todos: todos)
 end

 defp list_todos() do
   Todo
   |> Repo.all()
   |> Repo.preload(:author)
 end


# In your VIEW, you can manipulate the transformation you want.

 def render("index.json", %{todos: todos}) do
  todos
  |> Enum.map(fn f -> 
  %{
    # you can add additional fields in here.
    title: f.title,
    author: f.author.name
  }
  end)

 end

Can't query associated table

3 Answers