今回はクエリにおけるテーブルの結合について見ていきましょう。データベースを使用する場合は複数のテーブルやサブクエリを結合してケースは沢山あります。BEAMには結合に関する便利な仕組みが用意されています。一部ではありますが、実用的な使い方ができそうなサンプルを紹介できればと思います。

• 初回から読みたい方はHaskellでDBを操作しよう　Beamチュートリアル　Part 1
• 前回のチュートリアルはHaskellでDBを操作しよう　Beamチュートリアル　Part 2
• このチュートリアルのソースコードはGithub

Beamにおけるテーブルの結合

BeamのQモナドはListモナドのような性質を持っています。まずはListモナドと比較しながらテーブルの結合を見ていきましょう。まずは次のようなコードを見てみましょう。aとbのリストのタプルを返す関数です。この場合はリストモナドの特性によりリストの全ての組み合わせを返します。

import Control.Monad

main :: IO ()
main = do
  print func

func :: [(Int, Int)]
func = do
  a <- [ 1, 2, 3, 4 ]
  b <- [ 1, 1, 1, 2 ]
  pure (a, b)

-- output [(1,1),(1,1),(1,1),(1,2),(2,1),(2,1),(2,1),(2,2),(3,1),(3,1),(3,1),(3,2),(4,1),(4,1),(4,1),(4,2)]

では次に先程のコードにguard関数を適用した場合を見てみましょう。guard関数を使うことで条件に一致した組み合わせのみを抽出しています。

import Control.Monad

main :: IO ()
main = do
  print func

func :: [(Int, Int)]
func = do
  a <- [ 1, 2, 3, 4 ]
  b <- [ 1, 1, 1, 2 ]
  guard (a == b)
  pure (a, b)

-- output [(1,1),(1,1),(1,1),(2,2)]

Beamのクエリもこのリストと同様の書き方をすることができます。以下のようなコードになります。

sample09 :: Connection -> IO ()
sample09 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    l1 <- all_ (posts blogDatabase)
    l2 <- all_ (categories blogDatabase)
    return (l1, l2)
  print li

チュートリアル2でも出てきたguard_関数によって抽出をしてみましょう。先程見たguard関数と同じように条件によって抽出することができます。

sample10 :: Connection -> IO ()
sample10 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    l1 <- all_ (posts blogDatabase)
    l2 <- all_ (categories blogDatabase)
    guard_ (categorySlug l2 ==. "web")
    return (l1, l2)
  print li

このコードはPostgreSQLではCROSS JOINで表現されます。先程のリストのコードとよく似ていますね。

１対1

ではここまでのコードを踏まえて１対１の関係を見ていきましょう。 usersテーブルとprofiesテーブルは1対1の関係になっています。ここではuserと、それに紐づくprofileを取り出してみます。

sample11 :: Connection -> IO ()
sample11 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    prof <- all_ (profiles blogDatabase)
    usr <- related_ (users blogDatabase) (profileUser prof)
    return (usr, prof)
  print li

1対1の書き方に便利な関数oneToOne_も用意されています。以下のように使います。

sample12 :: Connection -> IO ()
sample12 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    usr <- all_ (users blogDatabase)
    prof <- oneToOne_ (profiles blogDatabase) profileUser usr
    return (usr, prof)
  print li

１対多

categoriesテーブルとpostsテーブルの関係は多対1の関係です。カテゴリーは多くの記事を保有しており、記事は一つのカテゴリーに属しています。今回は特定のカテゴリーに属する記事を取得することを考えます。基本的には１対１の考え方と同じです。

sample13 :: Connection -> IO ()
sample13 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    po <- all_ (posts blogDatabase)
    ca <- related_ (categories blogDatabase) (postCategory po)
    return (ca, po)
  print li

1対多の書き方に便利な関数oneToMany_も用意されています。以下のように使います。

sample14 :: Connection -> IO ()
sample14 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    ca <- all_ (categories blogDatabase)
    po <- oneToMany_ (posts blogDatabase) postCategory ca
    return (ca, po)
  print li

すでにお気づきかと思いますがoneToOne_とoneToMany_は同じ実装です。そして後に触れますがどちらもjoin_関数で実装されています。これは任意のクエリの結合の項で紹介します。

多対多

ProfileやCategoryと異なりTagとPostの関係は多対多の関係です。記事は複数のタグに属し、タグもまた複数の記事を保有する関係です。このような場合、中間テーブル(結合テーブル, junction table, join tableとも呼ばれる)を用意することが一般的です。今回もposts_tagsテーブルを用意しています。

多対多の場合に便利なmanyToMany_関数が用意されていますので、そちらを使いましょう。以下の例のように使います。

sample15 :: Connection -> IO ()
sample15 c = do
  let s = "haskell" :: Text
  li <- runBeamPostgresDebug putStrLn c $ do
    runSelectReturningList $ select $ do
      manyToMany_ (posts_tags blogDatabase)
                  posttagPost
                  posttagTag
                  (all_ (posts blogDatabase))
                  (filter_ (\t -> tagSlug t ==. val_ s) $ all_ (tags blogDatabase))
  print li

任意の結合

join_関数を使えば任意のテーブルをINNER JOIN結合することができます。例えばoneToMnay_関数で一対多の関係もjoin_関数で再現することができます。実はいままで紹介してきたrelated_, oneToMnay_, oneToOne_などの関数はjoin_関数で実装されています。よって(手間はかかりますが)全てjoin_関数で書くことは可能です。

書き方は

join_ 結合したいテーブル　結合条件

となります。ではサンプルを見てみましょう。ここではoneToMany_を使用したsample14をjoin_を用いて書き直してみましょう。

sample16 :: Connection -> IO ()
sample16 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    ca <- all_ (categories blogDatabase)
    join_ (posts blogDatabase) (\p -> postCategory p ==. primaryKey ca)
  print li

Left Join

BeamはLEFT JOINをサポートしています。LEFT JOINを実現するためには関数leftJoin_を使用します。書き方としては

leftJoin_ クエリ 結合条件

ここではposts_tagsテーブルとtagsテーブルをLEFT JOINしてみましょう。以下のコードのようになります。

sample17 :: Connection -> IO ()
sample17 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    tg <- all_ (tags blogDatabase)
    pt <- leftJoin_ (all_ (posts_tags blogDatabase)) (\p -> posttagTag p ==.  primaryKey tg)
    return (tg, pt)
  print li

サブクエリの結合

BEAMにおいてはサブクエリのは特別なことをしなくても任意のクエリを結合可能です。しかし明示的にsubselect_を用いてサブクエリを生成することも可能です。以下にサブクエリを生成して結合する例を示します。

sample18 :: Connection -> IO ()
sample18 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    sub <- subquery
    pt <- leftJoin_ (all_ (posts_tags blogDatabase)) (\p -> posttagTag p ==.  primaryKey sub)
    return (sub, pt)
  print li
  where
    target = "haskell" :: Text
    subquery = subselect_ $ filter_ (\t -> tagSlug t ==. val_ target) $ all_ (tags blogDatabase)

結合されたテーブルに対するOrderBy句、Where句

さてここまで様々なテーブルの結合を見てきました。この結合したテーブルに対してWHERE句、OrderBy句、LIMIT句などを付けることで複雑なデータ取得が可能となります。基本的には一つのテーブルの場合と変わりませんが、書き方を見ていきましょう。

sample19 :: Connection -> IO ()
sample19 c = do
  li <- runBeamPostgresDebug putStrLn c $ runSelectReturningList $ select $ do
    limit_ 10 $ orderBy_ (\(u, _) -> asc_ (userName u)) $ do 
      filter_ (\(u, _) -> userName u ==. "Chatora") $ do
        usr <- all_ (users blogDatabase)
        prof <- oneToOne_ (profiles blogDatabase) profileUser usr
        return (usr, prof)
  print li

SQLのWHERE句の部分はguard_を用いる方法もありますが、ここではfilter_関数を用いる方法について考えます。filter_やorder_関数を使う場合はreturnする型に合わせる必要があります。

まとめ

ここまで複数のクエリを結合する場合の書き方を見てきました。今回は外部キーがNULLを含む場合などは紹介できませんでした。不足分については公式チュートリアルをご覧ください。

もうBeamの使い方には慣れてきましたよね。次回は集計について扱っていきます。