Elixir Ectoのpreloadを1回のクエリーで実行する

DBはmysqlを使ってます。

サンプルテーブル作成

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `prefecture_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prefectures` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

lib/sample/schemas/user.ex

defmodule Sample.User do

  use Ecto.Schema

  schema "users" do
    field :name, :string
    timestamps(inserted_at: :created_at)

    has_one :prefecture, Sample.Prefecture, foreign_key: :id
  end

end

lib/sample/schemas/prefecture.ex

defmodule Sample.Prefecture do

  use Ecto.Schema

  schema "prefectures" do
    field :name, :string
    timestamps(inserted_at: :created_at)
  end

end

userテーブルのデータ取得

 Sample.User
 |> Sample.Repo.one()
 |> IO.inspect()

# SELECT u0.`id`, u0.`name`, u0.`created_at`, u0.`updated_at` FROM `users` AS u0 []

%Sample.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
 created_at: ~N[0000-00-00 00:00:00.000000], id: 1, name: "test",
 prefecture: #Ecto.Association.NotLoaded<association :prefecture is not loaded>,
 updated_at: ~N[0000-00-00 00:00:00.000000]}

userテーブルのデータ取得(関連付き)

 Sample.User
 |> Sample.Repo.one()
 |> Sample.Repo.preload(:prefecture) # prefectureのデータ取得
 |> IO.inspect()

# SELECT u0.`id`, u0.`name`, u0.`created_at`, u0.`updated_at` FROM `users` AS u0 []
# SELECT p0.`id`, p0.`name`, p0.`created_at`, p0.`updated_at`, p0.`id` FROM `prefectures` AS p0 WHERE (p0.`id` = ?) [1]

preloadを使用するとschemaで定義した関連テーブルのデータを取得できる
ただしこの方法だとクエリーが2回実行される

%Sample.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
 created_at: ~N[0000-00-00 00:00:00.000000], id: 1, name: "test",
 prefecture: %Sample.Prefecture{__meta__: #Ecto.Schema.Metadata<:loaded, "prefectures">,
  created_at: ~N[0000-00-00 00:00:00.000000], id: 1, name: "県名",
  updated_at: ~N[0000-00-00 00:00:00.000000]},
 updated_at: ~N[0000-00-00 00:00:00.000000]}

userテーブルのデータをjoinで取得(関連付き)

 Sample.User
 |> join(:inner, [p], _ in assoc(p, :prefecture))
 |> preload([..., p], [prefecture: p])
 |> Sample.Repo.one()
 |> IO.inspect()

# SELECT u0.`id`, u0.`name`, u0.`created_at`, u0.`updated_at`, p1.`id`, p1.`name`, p1.`created_at`, p1.`updated_at` FROM `users` AS u0 INNER JOIN `prefectures` AS p1 ON p1.`id` = u0.`id` []

%Sample.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
 created_at: ~N[0000-00-00 00:00:00.000000], id: 1, name: "test",
 prefecture: %Sample.Prefecture{__meta__: #Ecto.Schema.Metadata<:loaded, "prefectures">,
  created_at: ~N[0000-00-00 00:00:00.000000], id: 1, name: "県名",
  updated_at: ~N[0000-00-00 00:00:00.000000]},
 updated_at: ~N[0000-00-00 00:00:00.000000]}

こちらの方法ならクエリーは1回ですむ

おまけ

中間テーブルを使う場合

lib/sample/schemas/user.ex

defmodule Sample.User do

  use Ecto.Schema

  schema "users" do
    field :name, :string
    timestamps(inserted_at: :created_at)

    has_one :prefecture, Sample.Prefecture, foreign_key: :id

    has_many :skill_relations, Sample.SkillRelation
    has_many :skills, through: [:skill_relations, :skill]
  end

end

lib/sample/schemas/skill.ex

defmodule Sample.Skill do

  use Ecto.Schema

  schema "skills" do
    field :name, :string
    timestamps(inserted_at: :created_at)
  end

end

lib/sample/schemas/skill_relation.ex

defmodule Sample.SkillRelation do

  use Ecto.Schema

  schema "skill_relations" do
    timestamps(inserted_at: :created_at)

    belongs_to :user, Sample.User
    belongs_to :skill, Sample.Skill
  end

end

 Sample.User
 |> join(:inner, [p], _ in assoc(p, :prefecture))
 |> preload([..., p], [prefecture: p])
 |> join(:inner, [s], _ in assoc(s, :skills))
 |> preload([..., s], [skills: s])
 |> Sample.Repo.one()
 |> IO.inspect()

# SELECT u0.`id`, u0.`name`, u0.`created_at`, u0.`updated_at`, p1.`id`, p1.`name`, p1.`created_at`, p1.`updated_at`, s2.`id`, s2.`name`, s2.`created_at`, s2.`updated_at` FROM `users` AS u0 INNER JOIN `prefectures` AS p1 ON p1.`id` = u0.`id` INNER JOIN `skill_relations` AS s3 ON s3.`user_id` = u0.`id` INNER JOIN `skills` AS s2 ON s2.`id` = s3.`skill_id` []