Closed stevemao closed 2 years ago
That sounds strange. Did you replace your left join using this pattern? When there are missing rows in the right query you are supposed to get nothingFields
in the right of the tuple. Is that not what you're getting?
proc () -> do
fieldsL <- qL -< ()
maybeFieldsR <- optionalRestrict qR -< curry cond fieldsL
returnA -< (fieldsL, maybeFieldsR)
Here's an example. The output is
*Main> run
[((1,10),Nothing),((2,20),Just (2,"Hello"))]
Right ()
{-# LANGUAGE Arrows #-}
module Main where
import Control.Arrow (returnA)
import Opaleye
import qualified Database.Postgres.Temp as T
import qualified Database.PostgreSQL.Simple as S
import qualified Database.PostgreSQL.Simple.Options as O
import GHC.IO.Exception (ExitCode)
qL = values [(1, 10), (2, 20)]
qR = values [(2, toFields "Hello"), (3, toFields "Good bye")]
exampleJoin :: Select ((Field SqlInt4, Field SqlInt4),
MaybeFields (Field SqlInt4, Field SqlText))
exampleJoin = proc () -> do
fieldsL@(x, t) <- qL -< ()
maybeFieldsR <- optionalRestrict qR -< (\(y, s) -> x .== y)
returnA -< (fieldsL, maybeFieldsR)
run = withTempDBConnection $ \conn ->
print =<< runSelectI conn exampleJoin
withTempDBConnection :: (S.Connection -> IO a)
-> IO (Either T.StartError a)
withTempDBConnection f = T.with $ \tempDB ->
f =<< S.connectPostgreSQL (T.toConnectionString tempDB)
Sorry @tomjaguarpaw , it's not optionalRestrict
causes my problem. I get maybeFieldsR
and then I need to aggregate on it. I use catMaybeFields
to get rid of the maybe but it's actually wrong and that causes my problem. I created another issue for that https://github.com/tomjaguarpaw/haskell-opaleye/issues/547
I'm trying to refactor all my
leftJoin
s to useoptionalRestrict
based on the docsIt compiles well but the query behaves differently.
when right query returns nothing, and left query returns something,
leftJoin
would still return rows with fields that come from right querynull
. ButoptionalRestrict
would just return empty rows.In the generated SQL, I see a rebind always true in the right table. Removing that and in the
where
it works as expected. I can't figure out what rebind does here. I believe simple code example like the one in the docs can replicate the problem but let me know if not. Thanks.