CODEWITHSUNDEEP
elixirphoenix-frameworkecto
KONDO Daisuke
KONDO Daisuke

Reputation: 304

Ecto: How to preload records with selecting another joined columns

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"},...]},
  ...
]

Upvotes: 17

Views: 6981

Answers (1)

Jos&#233; Valim
Jos&#233; Valim

Reputation: 51429

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]]

Upvotes: 23

Related Questions