我一直试图在项目中使用Opaleye运行左联接,但无法编译代码。我从两个"模型"开始,它们表示相关联的表:
第一:
data ModelA' a b = Model { primA :: a, foreignA :: b }
type ModelA = ModelA' UUID UUID
type ModelAColumn = ModelA' (Column PGUuid) (Column (Nullable PGUuid))
$(makeAdaptorAndInstance "pModelA" ''ModelA')
table :: Table ModelAColumn ModelAColumn
table = Opaleye.table "model_a" $ pModelA (ModelA (tableColumn "uuid") (tableColumn "foreign"))
还有:
data ModelB' a b = Model { primB :: a, valB :: b }
type ModelB = ModelB' UUID String
type ModelBColumn = ModelB' (Column PGUuid) (Column PGText)
$(makeAdaptorAndInstance "pModelB" ''ModelB')
table :: Table ModelBColumn ModelBColumn
table = Opaleye.table "model_b" $ pModelB (ModelB (tableColumn "uuid") (tableColumn "val"))
正如类型所反映的,ModelA不能有任何ModelB关联。
我需要一个查询来获得foreignA==primB上表之间的左联接所给出的(ModelA,Maybe ModelB)对。我本来以为它看起来像:
doJoin :: Connection -> IO [(ModelA, Maybe ModelB)]
doJoin conn = runQuery conn query
where
query :: Query (ModelAColumn, Maybe ModelBColumn)
query = leftJoin (queryTable ModelA.table) (queryTable ModelB.table) ((ma, mb) -> foreignA ma .== primB mb)
但这并不奏效。我还尝试了多种变体,特别是我在查询中替换了类型签名,以明确声明右侧列的可空性:
query :: Query (ModelAColumn, (Column (Nullable PGUuid), Column (Nullable PGText))
但这失败了:
没有Data.Profunctor.Product.Default.Class.Default的实例Opaley.Internal.Join.NullMaker ModelBColumn(Column(NullablePGUuid),列(可为Null的PGText)。
如何在Opaleye中进行此查询?
这里有几个误解。我在下面制作了一个完整的工作版本。
首先,leftJoin
的返回类型不是
Query (ModelAColumn, Maybe ModelBColumn)
你必须做
type ModelBNullableColumn = ModelB' (Column (Nullable PGUuid))
(Column (Nullable PGText))
然后使用
Query (ModelAColumn, ModelBNullableColumn)
其次,runQuery
的返回类型不是
IO [(ModelA, Maybe ModelB)]
你必须做
type ModelBMaybe = ModelB' (Maybe UUID) (Maybe String)
并使用
IO [(ModelA, ModelBMaybe)]
这些差异的原因是Nullable
和Maybe
必须直接应用于每一列,而ModelBColumn
和ModelB
中的值不能应用于整个值。
(还有一些奇怪的语法错误,比如
ModelA { tableColumn "uuid", tableColumn "foreign" }
这意味着您的代码没有编译的希望。我也修好了。)
{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE MultiParamTypeClasses #-}
import Opaleye hiding (table)
import qualified Opaleye
import Data.Profunctor.Product.TH
import Database.PostgreSQL.Simple hiding (Query)
import Data.UUID
data ModelA' a b = ModelA { primA :: a, foreignA :: b }
type ModelA = ModelA' UUID (Maybe UUID)
type ModelAColumn = ModelA' (Column PGUuid) (Column (Nullable PGUuid))
$(makeAdaptorAndInstance "pModelA" ''ModelA')
modelAtable :: Table ModelAColumn ModelAColumn
modelAtable = Opaleye.table "model_a" $ pModelA ModelA { primA = tableColumn "uuid", foreignA = tableColumn "foreign" }
data ModelB' a b = ModelB { primB :: a, valB :: b }
type ModelB = ModelB' UUID String
type ModelBMaybe = ModelB' (Maybe UUID) (Maybe String)
type ModelBColumn = ModelB' (Column PGUuid) (Column PGText)
type ModelBNullableColumn = ModelB' (Column (Nullable PGUuid)) (Column (Nullable PGText))
$(makeAdaptorAndInstance "pModelB" ''ModelB')
modelBtable :: Table ModelBColumn ModelBColumn
modelBtable = Opaleye.table "model_b" $ pModelB ModelB { primB = tableColumn "uuid", valB = tableColumn "val" }
doJoin :: Connection -> IO [(ModelA, ModelBMaybe)]
doJoin conn = runQuery conn query
where
query :: Query (ModelAColumn, ModelBNullableColumn)
query = leftJoin (queryTable modelAtable) (queryTable modelBtable) ((ma, mb) -> matchNullable (pgBool False) (.== primB mb) (foreignA ma))
main :: IO ()
main = return ()
由于有人提出并回答了这个问题,Opaleye引入了一种更简单的左联接方法。查看名为optional
的函数。
尝试更改以下内容:
query :: Query (ModelAColumn, Maybe ModelBColumn)
至
query :: Query ((Column PGUuid) (Column (Nullable PGUuid))
, (Column (Nullable PGUuid)) (Column (Nullable PGText)))
尝试在不调用runQuery
的情况下键入check。一旦成功,请返回解决方案的其余部分。
您需要使用Functional Join
,特别是leftJoinF
。
看看这个https://hackage.haskell.org/package/opaleye-0.5.4.0/docs/Opaleye-FunctionalJoin.html
您需要在两个表上提供select的结果,作为它的第4个和第5个参数。相当于foreignA == primB
作为第三个参数。
对于第二个参数,您必须指定一些默认值,只要返回的Maybe ModelB
是Nothing
,就应该使用该值