inner_join.hs

inner_join.hs

#!/usr/bin/env stack
{- stack
     --resolver lts-6.24
     --install-ghc
     runghc
     --package yesod
     --package blaze-html
     --package persistent
     --package text
     --package bytestring
     --package persistent-postgresql
     --package persistent-template
     --package esqueleto
     --package monad-logger
     --package mtl
-}

{-# LANGUAGE EmptyDataDecls             #-}
{-# LANGUAGE FlexibleContexts           #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE TypeFamilies               #-}
import           Control.Monad.IO.Class  (liftIO)
import qualified Database.Esqueleto as E
import Database.Esqueleto (LeftOuterJoin)
import           Database.Persist
import           Database.Persist.Postgresql
import           Database.Persist.TH
import Control.Monad.Logger
import Control.Monad.Reader

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Author
    fname String
    lname String
    deriving Show
Book
    title String
    isbn String
    deriving Show
AuthorBook
    authorId AuthorId
    bookId BookId
|]

loadData :: MonadIO m => ReaderT SqlBackend m ()
loadData = do
  a1 <- insert $ Author "Simon" "PJ"
  a2 <- insert $ Author "Erik" "Meijer"

  b1 <- insert $ Book "title1" "dkfa1"
  b2 <- insert $ Book "title2" "2kdfja"

  insert_ $ AuthorBook a1 b1
  insert_ $ AuthorBook a1 b2
  insert_ $ AuthorBook a2 b1
  insert_ $ AuthorBook a2 b2

connStr = "host=localhost dbname=test user=postgres password=postgres port=5432"

testQuery :: MonadIO m => ReaderT SqlBackend m [(Entity Author, Entity Book)]
testQuery = E.select $ E.from $ \(p `E.InnerJoin` b ) -> do
              E.on (E.just (p E.^. PersonId) E.==. b E.?. BlogPostAuthorId)
              E.where_ $ (p E.^. PersonId) `E.in_` E.valList [toSqlKey 2]
              return (p,b)


main :: IO ()
main =
  runStderrLoggingT $
  withPostgresqlPool connStr 10 $
  \pool ->
     liftIO $
     do flip runSqlPersistMPool pool $
          do runMigration migrateAll
             -- loadData
             dat <- testQuery
             liftIO $ mapM_ print dat >> putStrLn ""
             return ()

testQuery = E.select $ E.from $ (ab E.InnerJoin a) -> do
E.on (E.just (ab E.^. AuthorBookId) E.==. ((a E.?. AuthorId)))
E.where_ $ (ab E.^. AuthorBookId) E.in_ E.valList [toSqlKey 1]
return a

select Author.* from Author as A
INNER JOIN AuthorBook ON A.id = AuthorBook.authorId
INNER JOIN Book ON AuthorBook.id = Book.id