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?

Answer 1

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

1. 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

Answer 2

The issue is here: has_many :todos, Main.Todo on TodoElixir.Repo.Migrations.CreateAuthors. It should be like

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

      timestamps()
    end

  end
end

Then you can query after preload data

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

Addtionally you should use TodoElixir.Main.Todo instead of Main.Todo and TodoElixir.User.Author instead of User.Author

Answer 3

You need to preload the relation explicitly:

todos = Main.list_todos()
|> Repo.preload(:todos) # don't forget to alias repo

If it throws an error then the relation is not referenced correctly, otherwise it will make a join query and you will have all relations in todos.

If you read the has_many/3 documentation, you can notice the following:

:foreign_key - Sets the foreign key, this should map to a field on the other schema, defaults to the underscored name of the current schema suffixed by _id

So in the case you have a foreign key with a different name you can explicitly use this parameter:

has_many :todos, Main.Todo, foreign_key: :author_id

Also you shouldn't add relations to migrations, in migrations you define only the structure and modifications you do to tables, so remove:

has_many :todos, Main.Todo

You can read more about what you can do in migrations here.