Deeply embedded ecto query - works to a point

Questions / Help
1

Give the following Ecto query, I’d expect to load milestones, which have tasks, which have comments, which belong to a single user:

def list_project_milestones_with_tasks(id) do
  query = from m in Milestone,
        where: m.project_id == ^id,
        preload: [{:tasks, [{:comments, :user}]}],
        order_by: m.position,
        select: map(
          m,
          [
            :id,
            :title,
            :description,
            :position,
            :inserted_at,
            tasks: [
              :id,
              :title,
              :description,
              :signoff_required,
              :position,
              :status,
              comments: [
                :id,
                :content,
                :inserted_at,
                user: [
                  :id,
                  :name
                ]
              ]
            ]
          ]
        )

  Repo.all(query)
  |> IO.inspect()
end

This query works right up until the user, but only returns nil for this field.

The query works fine if I remove the select filter, but I need to return these specific fields for the sake of formatting into JSON.

Any help is appreciated!

2

I can’t help but feel that because the user is a struct, rather than a list that it should be requested in a slightly different way?

3

I’m not sure why it doesn’t work without seeing more code. (User, etc)

That said, while it’s good practice to return only the fields you need, the part of “for the sake of JSON” makes me think if you shouldn’t use Ecto’s @derive instead.

Check out http://blog.trenpixster.info/til-about-ecto-schema-derive/

4

@derive looks like exactly what I’m looking for, thanks @hlx!

For the sake of future readers, my User modules looks like:

defmodule Drum.Coherence.User do
  @moduledoc false
  use Ecto.Schema
  use Coherence.Schema

  schema "users" do
    field :name, :string
    field :email, :string
    coherence_schema()

    many_to_many :projects, Drum.Projects.Project, join_through: "users_projects"
    has_many :comments, Drum.Projects.Comment

    timestamps()
  end
end

My Comment modules looks like:

defmodule Drum.Projects.Comment do
  use Ecto.Schema
  import Ecto.Changeset

  alias Drum.Projects.Comment  
  alias Drum.Projects.Task
  alias Drum.Coherence.User

  schema "comments" do
    field :content, :string
    belongs_to :user, User
    belongs_to :task, Task

    timestamps()
  end
end

(changesets not shown in either for the sake of clarity)

Thanks again for taking the time to reply.

5

What if you needed two different representations of a user? It looks like @derive cannot handle that case (as far as I can tell)

6

This is something I’ve run into as well. I ended up created private functions to clean up the redundant associations and meta data, rather than trying to use the select option in the query.

I’m not sure if it’s the best solution, but it’s working for the moment.