ElixirでSI開発入門 #5 Ectoで自由にSQLを書いて実行する(参照編)

（この記事は、「Elixir or Phoenix Advent Calendar 2017」の24日目です）

昨日は、@piacere さんの「Excelから関数型言語マスター5回目：Webにグラフ表示」でした

はじめに

Elixirで実際にプロダクト開発した経験からサンプルコードを交えて解説する本連載
今回はEctoでSQLのビルドを行わずに任意のSQLを実行する実装です。

Ecto。ElixirSchoolにもサンプルいろいろあってサンプル実装している間は楽しいのですが、載っているのは大体超シンプルなCRUDの例です。
実際のプロダクション開発でJoinや副照会バンバンやる実装が必要になってくると、
「え～とこういうリレーションでこんなSQLになるから～」とごちゃごちゃ考えながら実装している自分がいて非常にもどかしくなります。

私のチームでは「Join、副照会が発生した時点でSQLを書く」という方針で実装していますのでその実装例を照会します。

今回は以下の実装を例に考えます

• PhoenixでItemモデルを実装する
• 将来の価格改定に対応するため、未来の有効開始日レコードを持つ履歴系テーブルとしてItemPriceを実装する

また、以下の環境で実装しました

• Elixir v1.6.1
• Phoenix v1.3.2
• Ecto v2.2.10
• PostgreSQL v10.2

本連載の記事はこちら
|> ElixirでSI開発入門 #1 Ectoで悲観的ロック
|> ElixirでSI開発入門 #2 Ectoで楽観的ロック
|> ElixirでSI開発入門 #3 主キーが"id "じゃない既存DBへの接続
|> [ElixirでSI開発入門 #4 本番パスワードを環境変数に持たせる]
(https://qiita.com/tuchiro/items/4ccba7e210c596c383af)
|> ElixirでSI開発入門 #5 Ectoで自由にSQLを書いて実行する(参照編)

　お知らせ　
「fukuoka.ex#11：DB／データサイエンスにコネクトするElixir」を6/22（金）19時に開催します
私も本連載で出してないSI開発ネタを出す予定ですので、
Elixirでプロダクト開発最前線に興味ある方はぜひぜひご応募ください！

開発手順　

プロジェクト〜モデルの作成

PhoenixプロジェクトとDBを作成

> mix phx.new ecto_adapters_sample --no-brunch
> cd ecto_adapters_sample
> mix ecto.create

モデルを作成

> mix phx.gen.html Items Item items name category from_date:datetime
> mix phx.gen.html Items ItemPrice item_id:references:items price from_date:datetime

ルーティングを追加

lib/ecto_adapters_sample_web/router.ex

defmodule EctoAdaptersSampleWeb.Router do
  use EctoAdaptersSampleWeb, :router

  〜　中略　〜

  scope "/", EctoAdaptersSampleWeb do
    pipe_through :browser # Use the default browser stack

    get "/", PageController, :index
    resources "/items", ItemController # <-- ルーティングを追加
    resources "/item_prices", ItemPriceController # <-- ルーティングを追加
  end

  〜　中略　〜

end

できあがったテーブルのSelect文を考える

この2つのテーブルからなる商品エンティティを照会し、
商品カテゴリ　商品名　現在の価格　価格の適用開始日
を取得するSQLを考えます。

以下のようなデータを例にします。

• items
  

• item_prices
  

※５月に５０円値上げ、来月には更に50円値上される予定。
　今日が2018年5月18日だとすると、id=2の価格500が現在の価格となる。

select
	i.category,
	i.name,
	p.price,
	p.from_date
from
	items i,
	item_prices p
where
	i.id = 1 and
	i.id = p.item_id and
	p.from_date = (
		select
			max( from_date )
		from
			item_prices
		where
			item_id = 1 and
			from_date <= '{現在日時}'
	);

これと同じ照会をEctoのクエリビルダーで構築したいでしょうか？
私はしんどいので、このSQLをEctoをつかって実行したいと思います。
※性能問題が出た時のクエリのチューニングもSQLベースで実行できるので楽です。

Ecto.Adapter.sqlでSQLを直接実行

SQLの直接実行に対応するのがEcto.Adapter.SQLです。

Ecto.Adapter.SQLをつかって先程のSQLを実行する実装がこちら

lib/ecto_adapters_sample/items/items.ex

defmodule EctoAdaptersSample.Items do

〜　中略　〜

def get_current_item_info(item_id, current_date) do
    sql = "
    select
    	i.category,
    	i.name,
    	p.price,
    	p.from_date
    from
    	items i,
    	item_prices p
    where
    	i.id = 1
    	and i.id = p.item_id
    	and p.from_date = (
    		select
    			max( from_date )
    		from
    			item_prices
    		where
    			item_id = $1 and
    			from_date <= $2
    	);
    "
    {:ok, current_datetime} = Ecto.DateTime.cast(current_date) # <-- 文字列引数想定　"2018-05-01T09:00:00Z"
    Ecto.Adapters.SQL.query(EctoAdaptersSample.Repo, sql, [item_id, current_datetime]) # <-- 第三引数のリスト要素がsqlの$1,$2に適用される
  end
end

非常に簡単です。
・・・が、戻り値に癖があります。

> iex(9)> EctoAdaptersSample.Items.get_current_item_info(1, "2018-05-18T09:00:00Z")

{:ok,
 %Postgrex.Result{
   columns: ["category", "name", "price", "from_date"],
   command: :select,
   connection_id: 98153,
   num_rows: 1,
   rows: [["調理器具", "炊飯器", "500", {{2018, 5, 1}, {9, 0, 0, 0}}]]
 }}

なんじゃ、こりゃ
いつもみてるEctoの結果↓とあまりに違うやん

iex(22)> EctoAdaptersSample.Items.list_items()
[
  %EctoAdaptersSample.Items.Item{
    __meta__: #Ecto.Schema.Metadata<:loaded, "items">,
    category: "調理器具",
    id: 1,
    inserted_at: ~N[2018-05-17 10:45:02.332777],
    name: "炊飯器",
    updated_at: ~N[2018-05-17 10:45:02.338692]
  }
]

これだと、通常のRepo.allを使った実装などと組み合わせる際に使いにくい為、
戻り値を変換するWrapperを実装します。
Resultのcolumnsとrowsをつかって、
[%{column: row},%{column: row},・・・]
の構造のマップリストに変換します。

Wrapper関数の実装

lib/ecto_adapters_sample/ecto_util.ex

defmodule EctoAdaptersSample.Utils.EctoUtil do
  @moduledoc """
  Ecto関連の操作機能
  """
  import Ecto.Query, warn: false

@doc """
  SQL直接実行
  ## information
  EctoのSQLビルダを使用せずにSQLを直接実行する
  Ecto.Adapters.SQL.query()のWrapper関数
  戻り値を[%{row1},%{row2}...]形式で返す。

  ## Examples
      iex> EctoUtil.query(MyApp.Repo, "select * from users", params)
      [${id: => 1, name: => "userA name" },${id: => 2, name: => "userB name" }]
  """
   @spec query(Repo, string, [list]) :: [list]
  def query(repo, sql, params) do
    Ecto.Adapters.SQL.query(repo, sql, params)
    |> result_to_map_list()
  end

  defp result_to_map_list(result) do
    columns = elem(result, 1).columns
    rows = elem(result, 1).rows
    list_maps = Enum.map(rows, fn row -> row_columns_to_map(row, columns) end)
  end

  defp row_columns_to_map(row, columns) do
    map_result =
      Enum.map(Enum.with_index(row, 0), fn {k, i} -> [Enum.at(columns, i), k] end)
      |> Enum.map(fn [a, b] -> {String.to_atom(a), b} end)
      |> Map.new()
  end
end

先程の実装を置き換えてみましょう。

lib/ecto_adapters_sample/items/items.ex

defmodule EctoAdaptersSample.Items do

〜　中略　〜

def get_current_item_info(item_id, current_date) do
    sql = "
    select
    	i.category,
    	i.name,
    	p.price,
    	p.from_date
    from
    	items i,
    	item_prices p
    where
    	i.id = 1
    	and i.id = p.item_id
    	and p.from_date = (
    		select
    			max( from_date )
    		from
    			item_prices
    		where
    			item_id = $1 and
    			from_date <= $2
    	);
    "
    {:ok, current_datetime} = Ecto.DateTime.cast(current_date) # <-- 文字列引数想定　"2018-05-01T09:00:00Z"
    # Ecto.Adapters.SQL.query(EctoAdaptersSample.Repo, sql, [item_id, current_datetime]) # <-- 第三引数のリスト要素がsqlの$1,$2に適用される
    EctoAdaptersSample.Utils.EctoUtil.query(EctoAdaptersSample.Repo, sql, [item_id, current_datetime]) # <--EctoAdaptersSample.Utils.EctoUtil.queryに載せ替え
  end
end

iex(23)> EctoAdaptersSample.Items.get_current_item_info(1, "2018-05-18T09:00:00Z")

[
  %{
    category: "調理器具",
    from_date: {{2018, 5, 1}, {9, 0, 0, 0}},
    name: "炊飯器",
    price: "500"
  }
]

だいぶ扱いやすくなったかと思います。

まとめ

• SQLの直接実行にはEcto.Adapters.SQL
• ただし、戻り値に癖がある
• Wrappした関数で戻り値の癖をいい感じに吸収し、単純なクエリ（Repoで実装）と複雑なクエリ（Adapter実装）を使い分けるのがおすすめ

いかがだったでしょうか。
これで複雑な条件の照会もサクッと実装できますね？

明日は、@twinbee さんの「Elixirから簡単にRustが呼べるRustler#2 クレートを使ってみる」です。