How can I compose Database.Esqueleto queries in a modular way such that after defining a "base" query and the corresponding result set, I can restrict the result set by adding additional inner joins and where expressions.
Also, how can I convert the base query that returns a list of entities (or field tuples) into a query that counts the result set since the base query is not executed as such, but a modified version of it with LIMIT and OFFSET.
The following incorrect Haskell code snippet adopted from the Yesod Book hopefully clarifies what I'm aiming at.
{-# LANGUAGE QuasiQuotes, TemplateHaskell, TypeFamilies, OverloadedStrings #-}
{-# LANGUAGE GADTs, FlexibleContexts #-}
import qualified Database.Persist as P
import qualified Database.Persist.Sqlite as PS
import Database.Persist.TH
import Control.Monad.IO.Class (liftIO)
import Data.Conduit
import Control.Monad.Logger
import Database.Esqueleto
import Control.Applicative
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
name String
age Int Maybe
deriving Show
BlogPost
title String
authorId PersonId
deriving Show
Comment
comment String
blogPostId BlogPostId
|]
main :: IO ()
main = runStdoutLoggingT $ runResourceT $ PS.withSqliteConn ":memory:" $ PS.runSqlConn $ do
runMigration migrateAll
johnId <- P.insert $ Person "John Doe" $ Just 35
janeId <- P.insert $ Person "Jane Doe" Nothing
jackId <- P.insert $ Person "Jack Black" $ Just 45
jillId <- P.insert $ Person "Jill Black" Nothing
blogPostId <- P.insert $ BlogPost "My fr1st p0st" johnId
P.insert $ BlogPost "One more for good measure" johnId
P.insert $ BlogPost "Jane's" janeId
P.insert $ Comment "great!" blogPostId
let baseQuery = select $ from $ \(p `InnerJoin` b) -> do
on (p ^. PersonId ==. b ^. BlogPostAuthorId)
where_ (p ^. PersonName `like` (val "J%"))
return (p,b)
-- Does not compile
let baseQueryLimited = (,) <$> baseQuery <*> (limit 2)
-- Does not compile
let countingQuery = (,) <$> baseQuery <*> (return countRows)
-- Results in invalid SQL
let commentsQuery = (,) <$> baseQuery
<*> (select $ from $ \(b `InnerJoin` c) -> do
on (b ^. BlogPostId ==. c ^. CommentBlogPostId)
return ())
somePosts <- baseQueryLimited
count <- countingQuery
withComments <- commentsQuery
liftIO $ print somePosts
liftIO $ print ((head count) :: Value Int)
liftIO $ print withComments
return ()