How to get reference to child table's records from a parent?

Phoenix Forum Questions / Help
, ,
1

Hi, I’m a beginner in Elixir so I’d appreciate any help that I get - especially if thoroughly explained! :grin:

Context:
I have a parent table called posts and a child table called log_post_comment that has a Foreign key pointing to posts’ primary key (See the Entity relation diagram here)

I want to export all information about the posts table onto an Excel sheet (.xlsl). I managed to export the post’s id, title, and body. However, I can’t seem to get hold of log_post_comment.

Question
How do I get the log_post_comment records that are connected to the current post? Right now it’s a one-to-one relationship, but will it be different if it’s a one-to-many relation?

Here is my code (lib/myapp_web/views/exports/post_view.ex)

defmodule Sandbox.Exports.PostView do
  use SandboxWeb, :view

alias Elixlsx.{Workbook, Sheet}

@header [
    "ID",
    "Title",
    "Body",
    "Number of comments"
  ]
def render("report.xlsx", %{posts: posts}) do
    report_generator(posts)
    |> Elixlsx.write_to_memory("report.xlsx")
    |> elem(1)
    |> elem(1)
  end
def report_generator(posts) do
    rows = posts |> Enum.map(&(row(&1)))
    %Workbook{sheets: [%Sheet{name: "Posts", rows: [@header] ++ rows}]}
  end
def row(post) do
    [
      post.id,
      post.title,
      post.body,
      post.log_post_comment.numOfComments # <-- ERROR
    ]
  end
end

Error Output:

** (exit) an exception was raised:
    ** (UndefinedFunctionError) function nil.numOfComments/0 is undefined
        nil.numOfComments()
        (sandbox) lib/sandbox_web/views/exports/post_view.ex:27: Sandbox.Exports.PostView.row/1
        (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
        (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
        (sandbox) lib/sandbox_web/views/exports/post_view.ex:19: Sandbox.Exports.PostView.report_generator/1
        (sandbox) lib/sandbox_web/views/exports/post_view.ex:13: Sandbox.Exports.PostView.render/2
        (phoenix) lib/phoenix/view.ex:472: Phoenix.View.render_to_iodata/3
        (phoenix) lib/phoenix/controller.ex:776: Phoenix.Controller.render_and_send/4
        (sandbox) lib/sandbox_web/controllers/exports/post_controller.ex:1: Sandbox.Exports.PostController.action/2
        (sandbox) lib/sandbox_web/controllers/exports/post_controller.ex:1: Sandbox.Exports.PostController.phoenix_controller_pipeline/2
        (phoenix) lib/phoenix/router.ex:352: Phoenix.Router.__call__/2
        (sandbox) lib/sandbox_web/endpoint.ex:1: SandboxWeb.Endpoint.plug_builder_call/2
        (sandbox) lib/plug/debugger.ex:136: SandboxWeb.Endpoint."call (overridable 3)"/2
        (sandbox) lib/sandbox_web/endpoint.ex:1: SandboxWeb.Endpoint.call/2
        (phoenix) lib/phoenix/endpoint/cowboy2_handler.ex:65: Phoenix.Endpoint.Cowboy2Handler.init/4
        (cowboy) d:/Documents/4elements/Phoenix-Sandbox/deps/cowboy/src/cowboy_handler.erl:37: :cowboy_handler.execute/2
        (cowboy) d:/Documents/4elements/Phoenix-Sandbox/deps/cowboy/src/cowboy_stream_h.erl:306: :cowboy_stream_h.execute/3
        (cowboy) d:/Documents/4elements/Phoenix-Sandbox/deps/cowboy/src/cowboy_stream_h.erl:295: :cowboy_stream_h.request_process/3
        (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
2

Hello @Shifat-4elements :wave:! And welcome to the community =)
It would be more helpful if you provided the code of Schemas and probably context… the piece that fetches posts from DB to pass into view to render.

Assuming in your Post schema you have something like:

schema "posts" do
  has_one :log_post_comment, LogPostComment
  ...
end

make sure when you query for posts you also preload: :log_post_comment like

posts_query = from p in Post, ...

posts_query
|> Repo.all()
|> Repo.preload(:log_post_comment)

or you could combine preload with the main query like:

posts_with_comment_query = from p in Post, ..., preload: :log_post_comment
Repo.all(posts_with_comment_query)

and now each post from the result struct should have log_post_comment attribute as you expect

See more about preload in docs Ecto.Query.preload/3

3 Likes
3

Hi @RudManusachi , thank you for the fast reply!

I see that in my context, I have preloaded log_post_comment. But it seems like I still can’t get hold of it in the view (as seen in lib/myapp_web/views/exports/post_view.ex in my original post).

Here is the schema for both post and log_post_comment:
Post:

defmodule Sandbox.Posts.Post do
  use Ecto.Schema
  import Ecto.Changeset

  alias Sandbox.Comments.Comment

  schema "posts" do
    field :body, :string
    field :title, :string
    has_many :comments, Comment
    has_one :log_post_comment, Sandbox.Logs.LogPostComment

    timestamps()
  end

  @doc false
  def changeset(post, attrs) do
    post
    |> cast(attrs, [:title, :body])
    |> validate_required([:title, :body])
  end
end

Log_post_comment:

defmodule Sandbox.Logs.LogPostComment do
  use Ecto.Schema
  import Ecto.Changeset

  alias Sandbox.Posts.Post

  schema "log_post_comment" do
    field :numOfComments, :integer
    belongs_to :post, Post

    timestamps()
  end

  @doc false
  def changeset(log_post_comment, attrs) do
    log_post_comment
    |> cast(attrs, [:numOfComments, :post_id])
    |> validate_required([:numOfComments, :post_id])
  end
end

Here is my context file (lib/myapp/posts.ex):
Note: What I use is the list_posts function

defmodule Sandbox.Posts do
  @moduledoc """
  The Posts context.
  """

  import Ecto.Query, warn: false
  alias Sandbox.Repo

  alias Sandbox.Posts
  alias Sandbox.Posts.Post
  alias Sandbox.Comments
  alias Sandbox.Logs
  alias Sandbox.Logs.LogPostComment

  require Logger

  @doc """
  Returns the list of posts.

  ## Examples

      iex> list_posts()
      [%Post{}, ...]

  """
  def list_posts do
    Post
    |> Repo.all()
    |> Repo.preload(:log_post_comment)
  end

  @doc """
  Returns the number of comments in a post.

  ## Examples

      TODO: add example in comment.

  """
  def get_number_of_comments(post_id) do
    post = Posts.get_post!(post_id) |> Repo.preload([:comments])
    Enum.count(post.comments)
  end

  @doc """
  Gets a single post.

  Raises `Ecto.NoResultsError` if the Post does not exist.

  ## Examples

      iex> get_post!(123)
      %Post{}

      iex> get_post!(456)
      ** (Ecto.NoResultsError)

  """
  def get_post!(id) do
    Post
    |> Repo.get!(id)
    |> Repo.preload(:log_post_comment)
  end

  @doc """
  Creates a post.

  ## Examples

      iex> create_post(%{field: value})
      {:ok, %Post{}}

      iex> create_post(%{field: bad_value})
      {:error, %Ecto.Changeset{}}

  """
  def create_post(attrs \\ %{}) do
    %Post{}
    |> Post.changeset(attrs)
    |> Ecto.Changeset.cast_assoc(:log_post_comment, with: &LogPostComment.changeset/2)
    |> Repo.insert()
  end

  @doc """
  Adds a comment to a post.

  ## Examples

      TODO: Comment example.

  """
  def add_comment(post_id, comment_params) do
    # Check if a log exists for this post. If not, create one.
    try do
      Repo.get_by!(LogPostComment, post_id: post_id)
    rescue
      Ecto.NoResultsError -> Logs.create_log_post_comment(%{numOfComments: 0, post_id: post_id})
    end

    # Update counter
    from(log in Sandbox.Logs.LogPostComment,
      update: [inc: [numOfComments: 1]],
      where: log.post_id == ^post_id
    )
    |> Repo.update_all([])

    comment_params
    |> Map.put("post_id", post_id)
    |> Comments.create_comment()
  end

  @doc """
  Updates a post.

  ## Examples

      iex> update_post(post, %{field: new_value})
      {:ok, %Post{}}

      iex> update_post(post, %{field: bad_value})
      {:error, %Ecto.Changeset{}}

  """
  def update_post(%Post{} = post, attrs) do
    post
    |> Post.changeset(attrs)
    |> Ecto.Changeset.cast_assoc(:log_post_comment, with: &LogPostComment.changeset/2)
    |> Repo.update()
  end

  @doc """
  Deletes a post.

  ## Examples

      iex> delete_post(post)
      {:ok, %Post{}}

      iex> delete_post(post)
      {:error, %Ecto.Changeset{}}

  """
  def delete_post(%Post{} = post) do
    Repo.delete(post)
  end

  @doc """
  Returns an `%Ecto.Changeset{}` for tracking post changes.

  ## Examples

      iex> change_post(post)
      %Ecto.Changeset{data: %Post{}}

  """
  def change_post(%Post{} = post, attrs \\ %{}) do
    Post.changeset(post, attrs)
  end
end

I call the list_posts function in my post_controller.ex:

def index(conn, _params) do
    posts = Posts.list_posts()
    render(conn, "index.html", posts: posts)
  end
4

The error function nil.numOfComments/0 tells you the issue. Your code references post.log_post_comment.numOfComments, but when you preload the log_post_comment association, it returns nil - that is, there is no associated record that can be loaded. Then when you try and do nil.numOfComments it tries to treat nil as a module name and call the function numOfComments, which obviously doesn’t exist.

If you needed to preload the data, you would get an %Ecto.Association.NotLoaded{} instead of nil.

4 Likes
5

Thank you @John-Goff ! I managed to solve it.

The reason why it wasn’t working is simply because there weren’t any log_post_comment associated with the specified post in my database. I’m not used to Elixir’s error messages, so I didn’t realize what was going on - thanks for the explanation.

I do have another question regarding this though:
How do I check if a post has a log_post_comment or not? I tried checking for

if post.log_post_comment == nil do
  Logger.info("Does not exist")
end

But it gives me the same error.

6

Depends what you mean by this:

  • Do you want to check in the DB?
  • Or you want to check if an already loaded object has the assoc currently?

If the latter, then please always use if is_nil(post.log_post_comment).

Please clarify on this? Even if you check and have the logger statement successfully execute, the object is still nil so the error persisting is quite expected.

1 Like
7

Awesome, it worked! Thank you @dimitarvp :grin:

Here’s my code for future reference (lib/myapp_web/views/exports/post_view.ex):

defmodule Sandbox.Exports.PostView do
  use SandboxWeb, :view

alias Elixlsx.{Workbook, Sheet}

@header [
    "ID",
    "Title",
    "Body",
    "Number of comments"
  ]

def render("report.xlsx", %{posts: posts}) do
    report_generator(posts)
    |> Elixlsx.write_to_memory("report.xlsx")
    |> elem(1)
    |> elem(1)
end

def report_generator(posts) do
    rows = posts |> Enum.map(&(row(&1)))
    %Workbook{sheets: [%Sheet{name: "Posts", rows: [@header] ++ rows}]}
end

def row(post) do
  numOfComments = 0
  if not is_nil(post.log_post_comment) do
    numOfComments = post.log_post_comment.numOfComments
  end

    [
      post.id,
      post.title,
      post.body,
      numOfComments
    ]
  end
end