编写数据库.Esqueleto查询，条件连接和计数

本文介绍了编写数据库.Esqueleto查询，条件连接和计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 如何以模块化方式撰写 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）其中_（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 Pimport qualified Database.Persist.Sqlite as PSimport Database.Persist.THimport Control.Monad.IO.Class (liftIO)import Data.Conduitimport Control.Monad.Loggerimport Database.Esqueletoimport Control.Applicativeshare [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|Person name String age Int Maybe deriving ShowBlogPost title String authorId PersonId deriving ShowComment 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 BlogPostON Person.id = BlogPost.authorIdWHERE Person.name LIKE "J%"AND BlogPost.id = Comment.blogPostIdNot 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查询，条件连接和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！