はじめに
前の記事から間があいてしまいましたが、Advent Calendarの季節ということで、なんとか再開してみました。
今回からしばらくJOINを含んだSELECT操作を扱います。Servantとは独立な話で純粋にEsqueletoの使い方の話ですが、WebAPIの実装のうちのかなりの部分はDB操作となることが多いですので、詳しく書いていこうと思います。
JOINにもいろいろありますが、私がこれまで実プロジェクトで使ってきた「INNER JOIN」と「LEFT OUTER JOIN」の2通りについて書く予定です。本記事ででは、まずINNER JOINについて書きます。
本記事は、「SQLで簡単なINNER JOINが書ける」ことを前提としています。書けるといっても、ONを使ったJOIN句が書けるのであれば大丈夫です。
Modelの変更について
ここまでの記事で使用したモデルでは、JOINを扱うには簡単過ぎましたので、改めてモデルを作り直しました。各テーブル、以下のような感じです。
- Account = アプリ利用者
- Box = 管理する物品を収納する（物理的な）箱。各BoxはいずれかのAccountに属する
- Stock = Boxに収納する物品の種類（Content）と個数を保持する。同時期には一種類の物品のみ保持するが、時期によって保持する物品が違うことがある。物品の種類が変わればStockのレコードを新たに作成する（すなわち、どのタイミングでも、1つのBoxに対して有効なStockは1つのみ、とする）
- Content - Boxに収納する種類の情報。
- Folder - Boxをグループ分けする時のグループ情報。各BoxはFolder設定があってもいいしなくてもいい
各テーブルは、レコード作成時刻としてstartTime, レコード終了時刻としてendTimeを持ちます。startTimeはNOT NULLですが、endTimeはレコードが有効である間はNULLを持ちます。そのためendTimeは論理削除判定にも使えます。
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Account
name Text
accountType AccountType
startTime UTCTime
endTime UTCTime Maybe
deriving Show
Box
name Text
accountId AccountId
folderId FolderId Maybe
startTime UTCTime
endTime UTCTime Maybe
deriving Show
Stock
boxId BoxId
contentId ContentId Maybe
count Int
startTime UTCTime
endTime UTCTime Maybe
deriving Show
Move
srcStockId StockId
destStockId StockId
timestamp UTCTime
Content
name Text
startTime UTCTime
endTime UTCTime Maybe
deriving Show
Folder
name Text
startTime UTCTime
endTime UTCTime Maybe
deriving Show
|]
また、各テーブルの中身は以下のようになっているものとします。値は本記事に使用する分だけ掲載しています。
mysql> select * from account;
+----+-----------+-----------------+---------------------+----------+
| id | name | account_type | start_time | end_time |
+----+-----------+-----------------+---------------------+----------+
| 1 | Account-1 | AccountTypeUser | 2018-12-16 22:57:03 | NULL |
| 2 | Account-2 | AccountTypeUser | 2018-12-16 22:57:03 | NULL |
+----+-----------+-----------------+---------------------+----------+
2 rows in set (0.00 sec)
mysql> select * from box;
+----+---------+------------+-----------+---------------------+----------+
| id | name | account_id | folder_id | start_time | end_time |
+----+---------+------------+-----------+---------------------+----------+
| 1 | Box-1-1 | 1 | NULL | 2018-12-16 22:57:13 | NULL |
| 2 | Box-1-2 | 1 | 1 | 2018-12-16 22:57:13 | NULL |
| 3 | Box-2-1 | 2 | NULL | 2018-12-16 22:57:13 | NULL |
+----+---------+------------+-----------+---------------------+----------+
3 rows in set (0.00 sec)
mysql> select * from folder;
+----+---------+---------------------+----------+
| id | name | start_time | end_time |
+----+---------+---------------------+----------+
| 1 | Group-1 | 2018-12-16 22:57:08 | NULL |
| 2 | Group-2 | 2018-12-16 22:57:08 | NULL |
+----+---------+---------------------+----------+
2 rows in set (0.00 sec)
INNER JOIN...の前に
INNER JOINの説明に入る前に、JOINがない時のシンプルなSELECTについて再掲します。Esqueletoでは、select,fromの後ろに関数を記述し、その関数でwhere_やreturnを記述することで、SQLを生成できるようになっています。下記の例では、Accountテーブルのnameが「Account-1」であるレコードをselectしています。
-- nameが'Account-1'であるレコードをSELECTする実装
ea_list <- select $ from $ \a -> do
where_ $ a ^. AccountName ==. val "Account-1"
return a
liftIO $ print ea_list
この例で生成されるSQLは以下の通りです（適当なところで改行しています）。
SELECT `account`.`id`, `account`.`name`, `account`.`account_type`,
`account`.`start_time`, `account`.`end_time`
FROM `account`
WHERE `account`.`name` = ?
; [PersistText "Account-1"]
またSELECTされた結果（Entity Account型の値）をprintすることで、下記のような表示が出てきます（こちらも適当の改行をいれています）。指定した名前に該当するレコードは1つなので、1つ分のレコードだけ表示されています。
[Entity
{entityKey = AccountKey {unAccountKey = SqlBackendKey {unSqlBackendKey = 1}}
, entityVal = Account {accountName = "Account-1"
, accountAccountType = AccountTypeUser
, accountStartTime = 2018-12-16 22:57:03 UTC
, accountEndTime = Nothing}}]
このように取得したEntity型の値は、すぐにAPIの戻り値の型に変換して返す、でもいいですし、各レコードのカラムにアクセスして他の処理に利用するのもいいでしょう。toApiAccountFE（「FE」はFromEntity」の意）という関数が以下のように定義されているとしたら、先の例のselectの戻り値「ea_list（Entity Accountのリスト、の意）」の型は「[Entity Account]（Entity Accountのリスト）」ですので、例えばtoApiAccountFE <$> ae_listとすることで「[ApiAccount]（ApiAccountのリスト）」が得られます。
-- Entity Account型をApiAccount型に変換する関数
toApiAccountFE :: Entity Account -> ApiAccount
toApiAccountFE (Entity aid a) =
ApiAccount { apiAccountId = aid
, apiAccountName = accountName a
}
シンプルなINNER JOIN
実装
前置きが非常に長くなりましたが、ここからが本題です。INNER JOINを生成するコードの実装例を1つ書きます。これはAccountとBoxをIDどうしでINNER JOINする例です。
box_list <- select $ from $ \(b `InnerJoin` a) -> do
on $ b ^. BoxAccountId ==. a ^. AccountId
where_ $ a ^. AccountName ==. val "Account-1"
return (a, b)
liftIO $ print box_list
INNER JOINを使ったSELECTのポイントは3つあります。
- 「from」の後ろの関数の引数がJoinする側とされる側の2つとなり、「InnerJoin」という中間演算子でつなげる（さらにその全体を括弧でくくる）
- 「on」に続けて、Joinの条件を書く
- 取得結果として必要なテーブルに該当する「関数の仮引数（例でいうと、aやb）を「return」の後にタプルで書く
最初の2つは、SQL文で「JOINを書く」「JOINの条件をONで記述する」に相当します。3つめはselectでとってきた値をその後のコードで利用するために書くものです。select自体の動作に限っていうと、returnの箇所は「return b」としても「return (a, b)」としても、どちらも動作します。実アプリでは、たいていの場面でJOINしたテーブルを全てタプルに書くことになると思います。
3つのポイントのうち、一番わかりにくいのは、2つめの「ON条件の書き方」でしょう。ここはちょっとわかりにくいだけでなく、JOINのパターン（INNER JOINかOUTER JOINか）や、条件に記載するカラムの属性によって書き方が変わってきます。そのため、JOINを含んだSELECTの実装のハードルは、ほぼこの「ONの書き方」だといっていいでしょう。
ONの部分はこのようになっています。じっくり眺めていくと、SQLの「on box.account_id = account.id」にように見えてくるのではないでしょうか？
on $ b ^. BoxAccountId ==. a ^. AccountId
SQLは大文字でも小文字でもどちらでも大丈夫なことが多いですが、ここのonは関数なので必ず小文字を使用します。aをAccountテーブル、bをBoxテーブルに紐付ける、としたい場合には、「a ^. AccountId」とすることで、SQLでいう「account.id」相当となります。「b ^. BoxAccountId」も同様に「box.account_id」相当となります。それらを「==.」でつなぎます。言うまでもないですが、「==.」はSQLでいう「=」相当です。
今回のJOINに限らず、「テーブルのカラムを表す」表現として「b ^. BoxAccountId」というパターンが頻出します。「^.」の前はfromの後ろに書く、関数の仮引数、「^.」の後ろはテーブルのカラムを記述します。テーブルのカラムは大文字で開始し（例えば「Box」続けてカラム名をキャメルケースで書きます。例でいうと「Box」に「AccountId」を続けるので「BoxAccountId」が「Boxテーブルのaccount_idカラム」となります。Haskellのコードは基本的にキャメルケースで書きますが、SQLに変換されるときには自動的にスネークケースに変換されます。「AccountId」はSQLとしては「account_id」となります。
なお、「Box」テーブルに「AccountId」カラムがあるために「BoxAccountId」という書き方をするとなると、「もし、BoxAccount、というテーブルを作ったら、そのIDはBoxAccountIdとなって区別がつかないのでは？」と思うかもしれません。実際にそういうコードを書くと、コンパイラの方で「名前が重複するからダメだよ」というエラーとなります。このような制約があるため、モデル定義でテーブル名やカラム名の命名には、若干の制約がでることになります。
生成されるSQL
このコードで生成されるSQLは以下のようになります。Accountテーブルの全カラムとBoxテーブルの全カラムがSELECTとFROMの間にあり、FROMの後にINNER JOIN句があることがわかると思います。
SELECT `account`.`id`, `account`.`name`, `account`.`account_type`,
`account`.`start_time`, `account`.`end_time`,
`box`.`id`, `box`.`name`, `box`.`account_id`,
`box`.`folder_id`, `box`.`start_time`, `box`.`end_time`
FROM `box` INNER JOIN `account` ON `box`.`account_id` = `account`.`id`
WHERE `account`.`name` = ?
; [PersistText "Account-1"]
値は以下のようなものになります。Acountテーブルの値に加えてBoxテーブルの値が返るため、JOINがないSELECTよりも多くの中身となっています。
[(Entity {entityKey = AccountKey
{unAccountKey = SqlBackendKey {unSqlBackendKey = 1}}
, entityVal = Account {accountName = "Account-1", accountAccountType = AccountTypeUser
, accountStartTime = 2018-12-16 22:57:03 UTC, accountEndTime = Nothing}}
,Entity {entityKey = BoxKey {unBoxKey = SqlBackendKey {unSqlBackendKey = 1}}
, entityVal = Box {boxName = "Box-1-1", boxAccountId = AccountKey {unAccountKey = SqlBackendKey {unSqlBackendKey = 1}}
, boxFolderId = Nothing, boxStartTime = 2018-12-16 22:57:13 UTC
, boxEndTime = Nothing}})
,(Entity {entityKey = AccountKey {unAccountKey = SqlBackendKey {unSqlBackendKey = 1}}
, entityVal = Account {accountName = "Account-1", accountAccountType = AccountTypeUser
, accountStartTime = 2018-12-16 22:57:03 UTC, accountEndTime = Nothing}}
,Entity {entityKey = BoxKey {unBoxKey = SqlBackendKey {unSqlBackendKey = 2}}
, entityVal = Box {boxName = "Box-1-2", boxAccountId = AccountKey {unAccountKey = SqlBackendKey {unSqlBackendKey = 1}}
, boxFolderId = Just (FolderKey {unFolderKey = SqlBackendKey {unSqlBackendKey = 1}})
, boxStartTime = 2018-12-16 22:57:13 UTC, boxEndTime = Nothing}})]
話がだいぶ長くなってしまったので、この値の使い方と、他のINNER JOINのケースについては次の記事で書きます。
まとめ
EsqueletoでのINNER JOINを使ったSELECT操作についての話が始まりました。INNER JOINもそうですが、OUTER JOINは特にパターンによる実装のバリエーションが幅広いです。できるだけ実例を挙げて書き続けていこうかと思っています。
次回はINNER JOINで取得した値の使い方と、INNER JOIN操作のバリエーションについて書きます。