Selecting fields in preloaded joins Ecto

You can use a pattern matching here to get only those fields You’re interested in, i.e. %{title: title, content:content, comments: comments, user: %{name: name}} = Repo.get!(post_query) and then You can pass comments though Enum.Map. In Phoenix app You can also use View - “show.json” approach to convert result of Your ECTO query to a Map with the fields that You need… Because anyway You can’t just encode ECTO query result to JSON…

This should make only one DB query.

post_query = 
  from p in Post,
    where: p.id == ^id,
    join: c in assoc(p, :comments),
    join: u in assoc(c, :user), 
    preload: [comments: {c, user: u}]

Regarding selecting association fields, you can do something like this (not tested)

post_query = 
  from p in Post,
    where: p.id == ^id,
    join: c in assoc(p, :comments),
    join: u in assoc(c, :user), 
    select: %{id: p.id, title: p.title, comments: p.comments, name: u.name}

Not sure exactly what you want to get back but that’s one way to select fields

Thank you @Linuus! I was missing the user: u on the end there. Still not entirely sure what that is doing?

Also the data structure I’m trying to achieve looks like the following:

%{
  content: "This is some content",
  id: "46b7e047-a1f1-4fe1-b7f1-91c2aeb5910d",
  inserted_at: #Ecto.DateTime<2016-06-29 11:49:53>,
  title: "This is a new post"
  comments: [%{
    comment: "Some comment!",
    id: "a94fc738-69e0-44e1-9e39-3204ad7f3878",
    inserted_at: #Ecto.DateTime<2016-07-01 22:48:41>,
    user: %{
      name: "Alaister Young",
    },
    %{
      comment: "Some comment!",
      id: "a94fc738-69e0-44e1-9e39-3204ad7f3879",
      inserted_at: #Ecto.DateTime<2016-07-01 22:48:42>,
      user: %{
        name: "Alaister Young",
      }
    }
  ]
}

The problem I’m having with this structure is I’m not sure how to deal with the list of comments with the nested users.

Thanks again!

The user: u tells ecto to use that join thingy for the preload and not issue another request to fetch that association. (I think… I’m quite new to Ecto myself.)

So this query:

post_query = 
  from p in Post,
    where: p.id == ^id,
    join: c in assoc(p, :comments),
    join: u in assoc(c, :user), 
    preload: [comments: {c, user: u}]

returns that structure, right? So what you want to do now is limit the number of selected fields for performance reasons?

That is completely correct!

I had a discussion about this on IRC and it seems to often be better to not join manually and just preload the data (and let it do multiple queries).

This would issue one request to the DB:

post_query = 
  from p in Post,
    where: p.id == ^id,
    join: c in assoc(p, :comments),
    join: u in assoc(c, :user), 
    preload: [comments: {c, user: u}]

But it would return a lot of excessive data because the join would cause the Post data to be sent for each comment (due to how SQL works). Ecto hides this though by throwing away the excessive data.
You can se this if you run something like the query I wrote before:

post_query = 
  from p in Post,
    where: p.id == ^id,
    join: c in assoc(p, :comments),
    select: %{id: p.id, comments: c}

It will return one struct for each Comment so if a Post has two comments:

[%{
  id: 1,
  comments: %{id: 123, text: "foo"}
}, %{
  id: 1, 
  comments: %{id: 124, text: "Next comment"}
}]`

You don’t see this behaviour when running without the select statement because Ecto takes care of mapping the data.

When just preloading:

post_query = 
  from p in Post,
    where: p.id == ^id,
    preload: [comments: [:user]]

This would issue three requests but no excessive data so in many cases it is actually more efficient (Ecto even does the requests in parallell when possible).
For example, if the same user has made all the comments, the user would only be fetched once. When joining, the user would be returned for each comment.

So, unless you know you have a bottleneck here it may not be worth it…

(I’m quite new to Ecto, so if someone sees something wrong here, please let me know! )

Ahh okay that makes a lot of sense! Thanks again

My only question now is a much more general Ecto question and that’s how to order the comments in descending order. I understand that it has something to do with order_by: [desc: c.inserted_at] but I’m not sure where to put this!

Thanks,
Alaister

I think I have solved this one:

comment_query = from c in Comment,
                order_by: [desc: c.inserted_at],
                preload: :user

post_query = from p in Post,
             where: p.id == ^id,
             preload: [comments: ^comment_query]

post = Repo.one(post_query)

Also would selecting only the fields I needed make my queries more performant? If so how might I do it with the preloaded users without the join?

Thanks,
Alaister

Hello, I have a problem when I want to use select in preload, please see this code

query = from u in UserSchema,
       where: u.id == ^id,
       left_join: c in assoc(u, :subscribers),
       preload: [subscribers: c],
       select: %{
         id: u.id,
         name: u.name,
         mobile: u.mobile,
         last_name: u.last_name,
         mobile: u.mobile,
         status: u.status,
         role: u.role,
         inserted_at: u.inserted_at,
         updated_at: u.updated_at,
         subscribers: c
       }
   Repo.one(query)
  end

and error:

** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query:

I should use select on UserSchema, how can I fix this ?

query = from u in UserSchema,
       where: u.id == ^id,
       left_join: c in assoc(u, :subscribers),
       preload: [subscribers: c]

   Repo.one(query)