Edited at

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

More than 1 year has passed since last update.

(この記事は、「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 本番パスワードを環境変数に持たせる

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

:stars::stars::stars::stars::stars: お知らせ :stars::stars::stars::stars::stars:

「fukuoka.ex#11:DB/データサイエンスにコネクトするElixir」を6/22(金)19時に開催します

私も本連載で出してないSI開発ネタを出す予定ですので、

Elixirでプロダクト開発最前線に興味ある方はぜひぜひご応募ください!

image.png


開発手順 


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

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

    image.png


  • item_prices

    image.png


※5月に50円値上げ、来月には更に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 クレートを使ってみる」です。