Ecto: How to preload records with selecting another joined columns

Question

Are there any ways to preload records by selecting another joined columns?

# table structure
# User 1---* Post 1---* PostTag *---1 Tag

# extract definition of scheme
scheme "posts" do
 ...
 has_many :post_tags, PostTag
 has_many :tags, [:post_tags, :tag]
end

Following pseudo-code expresses my goal(but not work).

query = from post in Post,
  join: user in User, on post.user_id == user.id,
  select: %{
    id: post.id,
    title: post.title,
    user_name: user.name, # <= column at joined table
  },
  preload: [:tags]
Repo.all(query)
#=> ** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query:`

I expect the result like this.

[
  %{id: 1, title: "AAA", user_name: "John", tags: [%{name: "elixir"},...]},
  %{id: 2, title: "BBB", user_name: "Mike", tags: [%{name: "erlang"},...]},
  ...
]

Miniver

Answer 1

As the error message says, you need to select the binding you gave in from when you are preloading, otherwise Ecto has no place to put the preloaded tags. Here is a simple answer:

query = from post in Post,
  join: user in User, on: post.user_id == user.id,
  select: {post, user.name},
  preload: [:tags]

By returning a tuple, you can have the full post and the user.name on the side. Another approach is to return both post and users as full structs:

query = from post in Post,
  join: user in User, on: post.user_id == user.id,
  preload: [:tags, user: user]

or if you don't want all fields:

query = from post in Post,
  join: user in User, on: post.user_id == user.id,
  preload: [:tags, user: user],
  select: [:id, :title, :user_id, user: [:name]]

Parody

Can I filter preloaded multiple record(e.g: tags) like user? I try to use custom query tag_q = from tag in Tag, select: [:id, :name], and pass preload section(preload: [tags: ^tag_q]). This can run, but tags fields are not filtered. – Miniver

You can do the same you did for users: select: [:id, :title, :user_id, user: [:name], tags: [:id, :name]]. The query should have worked too. – Sethsethi

Many thanks. That's exactly I wanted to do. When I try select: [:id, :user_id, user: [:name], tags: [:name]], I get NoPrimaryKeyValueError. But I think that's not a big problem. I can get result as desired by select: [:id, :user_id, user: [:id, :name], tags: [:name]]. – Miniver