由YesOneMany在Yesod [英] Baffled by selectOneMany in Yesod
问题描述
甜蜜但简单,持久连接如何工作?考虑以下模型:
Sweet but simple, how do Persistent joins work? Consider the following model:
Person
number Int
numberOfEyes Int
firstName FirstnamesId
lastName LastnamesId
Lastnames
lastname String
Firstnames
firstname String
假设我只有一个人的数字,我如何检索他的全名和他的眼睛的数量?
Assuming I only have the number of a Person, how do I retrieve his full name and the number of his eyes?
我试图通过< a href =https://github.com/snoyberg/haskellers =nofollow> haskellers.org来源,但找不到任何联接示例。我也在yesod图书中查看了加入一章,但它只使我的眼睛旋转。
I tried looking through the haskellers.org source but couldn't find any examples of joins. I also checked out the chapter on joins in the yesod book but it only made my eyes spin. The level of my Haskell knowledge is very low so be gentle.
推荐答案
这里有两种相同的结果类型的方法:
Here are two ways with identical result type:
- 新输入的sql,基于 esqueleto 来自Felipe Lessa的包是
-
和以前的rawSql方式
- the new typed sql, based on the "esqueleto" package from Felipe Lessa which is persistent based
and the previous rawSql way
只需添加1或2作为测试的参数
just add 1 or 2 as argument to the test
{- file prova.hs-}
{-# LANGUAGE QuasiQuotes, TemplateHaskell, TypeFamilies, OverloadedStrings #-}
{-# LANGUAGE GADTs, FlexibleContexts, ConstraintKinds, ScopedTypeVariables #-}
import Prelude hiding (catch)
import Control.Exception
import Database.Persist
import Database.Persist.Sqlite
import Database.Persist.TH
import Control.Monad.IO.Class (liftIO)
import Data.Text (Text)
import Database.Persist.Quasi
import Database.Esqueleto as Esql
import Database.Persist.GenericSql (SqlPersist, rawSql)
import Control.Monad.Logger (MonadLogger)
import Control.Monad.Trans.Resource (MonadResourceBase)
import System.Environment (getProgName, getArgs)
import System.Exit (exitSuccess, exitWith, ExitCode(..))
import Text.Printf (printf)
import QQStr(str) -- heredoc quasiquoter module
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persist|
Person
number Int
numberOfEyes Int
firstName FirstnamesId
lastName LastnamesId
UniquePersonNumber number
deriving Show
Lastnames
lastname String
deriving Show
Firstnames
firstname String
deriving Show
|]
-- the esqueleto way
-- with this type annotation it could be run in a yesod handler with ''runDB''
getPersonInfoByNumber :: (PersistQuery SqlPersist m, MonadLogger m, MonadResourceBase m) => Int -> SqlPersist m (Maybe (Int, String, String))
getPersonInfoByNumber pNumber = do
result <- select $ from $ \(fn `InnerJoin` p `InnerJoin` ln) -> do
on ((p ^. PersonFirstName) Esql.==. (fn ^. FirstnamesId))
on ((p ^. PersonLastName) Esql.==. (ln ^. LastnamesId))
where_ ((p ^. PersonNumber) Esql.==. val pNumber)
return (p , fn, ln)
case result of
[(Entity _ p, Entity _ fn, Entity _ ln)] -> return $ Just (personNumberOfEyes p, firstnamesFirstname fn, lastnamesLastname ln)
_ -> return Nothing
-- the rawSql way
stmt = [str|SELECT ??, ??, ??
FROM Person, Lastnames, Firstnames
ON Person.firstName = Firstnames.id
AND Person.lastName = Lastnames.id
WHERE Person.number = ?
|]
getPersonInfoByNumberRaw :: (PersistQuery SqlPersist m, MonadLogger m, MonadResourceBase m) => Int -> SqlPersist m (Maybe (Int, String, String))
getPersonInfoByNumberRaw pNumber = do
result <- rawSql stmt [toPersistValue pNumber]
case result of
[(Entity _ p, Entity _ fn, Entity _ ln)] -> return $ Just (personNumberOfEyes p, firstnamesFirstname fn, lastnamesLastname ln)
_ -> return Nothing
main :: IO ()
main = do
args <- getArgs
nomProg <- getProgName
case args of
[] -> do
printf "%s: just specify 1 for esqueleto or 2 for rawSql.\n" nomProg
exitWith (ExitFailure 1)
[arg] -> (withSqliteConn ":memory:" $ runSqlConn $ do
runMigration migrateAll
let myNumber = 5
fnId <- insert $ Firstnames "John"
lnId <- insert $ Lastnames "Doe"
-- in case of insert collision, because of UniquePersonNumber constraint
-- insertUnique does not throw exceptions, returns success in a Maybe result
-- insert would throw an exception
maybePersId <- insertUnique $ Person {personNumber = myNumber, personNumberOfEyes=2,
personFirstName = fnId, personLastName = lnId}
info <- case arg of
"1" -> getPersonInfoByNumber myNumber
_ -> getPersonInfoByNumberRaw myNumber
liftIO $ putStrLn $ show info
)
`catch` (\(excep::SomeException) ->
putStrLn $ "AppSqlError: " ++ show excep)
heredoc quasiquoter的额外模块
extra module for heredoc quasiquoter
module QQStr(str) where
import Prelude
import Language.Haskell.TH
import Language.Haskell.TH.Quote
str = QuasiQuoter { quoteExp = stringE, quotePat = undefined
, quoteType = undefined, quoteDec = undefined }
执行:
gabi64@zotac-ion:~/webs/yesod/prova$ ./cabal-dev/bin/prova 1
Migrating: CREATE TABLE "Person"("id" INTEGER PRIMARY KEY,"number" INTEGER NOT NULL,"numberOfEyes" INTEGER NOT NULL,"firstName" INTEGER NOT NULL REFERENCES "Firstnames","lastName" INTEGER NOT NULL REFERENCES "Lastnames")
Migrating: CREATE TABLE "Lastnames"("id" INTEGER PRIMARY KEY,"lastname" VARCHAR NOT NULL)
Migrating: CREATE TABLE "Firstnames"("id" INTEGER PRIMARY KEY,"firstname" VARCHAR NOT NULL)
Just (2,"John","Doe")
这篇关于由YesOneMany在Yesod的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!