编写数据库.Esqueleto查询，条件连接和计数 [英] Composing Database.Esqueleto queries, conditional joins and counting
问题描述
如何以模块化方式撰写 Database.Esqueleto 查询，以便在定义基本查询之后和相应的结果集，我可以通过添加额外的内部连接和表达式来限制结果集。
另外，如何转换返回列表的基本查询实体（或字段元组）转换为对结果集进行计数的查询，因为基本查询不会执行，而是使用LIMIT和OFFSET对其进行修改。
以下来自 Yesod Book 的不正确的Haskell代码片段有望澄清我所瞄准的内容。
{ - ＃LANGUAGE QuasiQuotes，TemplateHaskell，TypeFamilies，OverloadedStrings＃ - }
{ - ＃LANGUAGE GADTs，FlexibleContexts＃ - }
导入合格Database.Persist为P
导入合格Database.Persist.Sqlite为PS
导入Database.Persist.TH
导入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字符串
年龄Int也许
派生显示
BlogPost
标题字符串
authorId PersonId
派生显示
评论
评论字符串
blogPostId BlogPostId
$]
$ b main :: IO（）
main = runStdoutLoggingT $ runResourceT $ PS.withSqliteConn：memory ：$ PS.runSqlConn $ do
runMigration migrateAll
johnId< - P.insert $ PersonJohn Doe$只需35
janeId< - P.insert $ PersonJane DoeNothing
jackId< - P.insert $ PersonJack Black$ Just 45
jillId< - P.insert $ PersonJill BlackNothing
blogPostId< - P.insert $ BlogPostMy fr1st p0stjohnId
P.insert $ BlogPost一个更好的方法johnId
P.insert $ BlogPostJane's janeId
.insert $ Comment太棒了！ blogPostId
让baseQuery =从$ \（p`InnerJoin` b） - > （p ^。PersonId ==。b ^。BlogPostAuthorId）
where_（p ^。PersonName`like`（valJ％））
return（p，b）
- 不编译
let baseQueryLimited =（，）< $> baseQuery< *> （限制2）
- 不编译
let countingQuery =（，）< $> baseQuery< *> （return countRows）
- 无效SQL
的结果let commentsQuery =（，）< $> baseQuery
*< *> （b ^。BlogPostId ==。c ^。CommentBlogPostId）
return（））
$ b（从$ \（b`InnerJoin` c） - > do
中选择$ $ $ b somePosts< - baseQueryLimited
count< - countingQuery
withComments< - commentsQuery
liftIO $ print somePosts
liftIO $ print（（head count）:: Value Int）
liftIO $ print withComments
return（）
对于 LIMIT 和 COUNT ，hammar的回答完全正确，所以我不会深入研究它们。我将重申一旦使用选择，您将无法再以任何方式更改查询。
对于 JOIN s，目前您无法执行 INNER JOIN 从（也不是（FULL | LEFT | RIGHT）OUTER JOIN s）有一个不同的。但是，您可以执行隐式连接。例如，如果您已经定义：$ \（p`InnerJoin` b） -
baseQuery =
- > （p ^。PersonId ==。b ^。BlogPostAuthorId）
where_（p ^。PersonName`like` val'J％）
return（p，b）
然后您可以说：
commentsQuery =
from $ \c - >
（p，b）< - baseQuery
其中_（b ^。BlogPostId ==。c ^。CommentBlogPostId）
return（p，b，c）
然后Esqueleto会产生如下内容：
SELECT ...
FROM Comment，Person INNER JOIN BlogPost
ON Person.id = BlogPost.authorId
WHERE Person.name LIKE J％
AND BlogPost.id = Comment.blogPostId
为 INNER JOIN s完成的工作。如果您需要执行 OUTER JOIN ，那么您必须重构代码，以便所有 OUTER JOIN s与相同（注意，你可以在 OUTER JOIN s之间进行隐式连接）。 p>
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 ()
For LIMIT and COUNT, hammar's answer is entirely correct so I'll not delve into them. I'll just reiterate that once you use select you'll not be able to change the query in any way again.
For JOINs, currently you are not able to do a INNER JOIN with a query that was defined in a different from (nor (FULL|LEFT|RIGHT) OUTER JOINs). However, you can do implicit joins. For example, if you have defined:
baseQuery =
from $ \(p `InnerJoin` b) -> do
on (p ^. PersonId ==. b ^. BlogPostAuthorId)
where_ (p ^. PersonName `like` val "J%")
return (p, b)
Then you may just say:
commentsQuery =
from $ \c -> do
(p, b) <- baseQuery
where_ (b ^. BlogPostId ==. c ^. CommentBlogPostId)
return (p, b, c)
Esqueleto then will generate something along the lines of:
SELECT ...
FROM Comment, Person INNER JOIN BlogPost
ON Person.id = BlogPost.authorId
WHERE Person.name LIKE "J%"
AND BlogPost.id = Comment.blogPostId
Not pretty but gets the job done for INNER JOINs. If you need to do a OUTER JOIN then you'll have to refactor your code so that all the OUTER JOINs are in the same from (note that you can do an implicit join between OUTER JOINs just fine).
这篇关于编写数据库.Esqueleto查询，条件连接和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
