Opaleye.Join

Left, right, and full outer joins.

The recommended way of performing joins in Opaleye

Opaleye supports inner joins, left/right joins and full outer joins. Instead of using them directly we recommend the following, which provide APIs that are more familiar to a Haskell programmer and more composable:

• Inner joins: use restrict directly (along with <*> or arrow notation)
• Left/right joins: use optionalRestrict
• Lateral left/right joins: use optional
• Full outer joins: use fullJoinF (If you have a real-world use case for full outer joins then we'd love to hear about it. Please open a new issue on the Opaleye project and tell us about it.)

optionalRestrict

Arguments

:: Default Unpackspec a a

=> Select a

Input query

-> SelectArr (a -> Field SqlBool) (MaybeFields a)

If any rows of the input query satisfy the condition then return them (wrapped in "Just"). If none of them satisfy the condition then return a single row of "Nothing"

Convenient access to left/right join functionality. Performs a LEFT JOIN under the hood and has behaviour equivalent to the following Haskell function:

optionalRestrict :: [a] -> (a -> Bool) -> [Maybe a]
optionalRestrict xs p =
   case filter p xs of []  -> [Nothing]
                       xs' -> map Just xs'

For example,

> let l = [1, 10, 100, 1000] :: [Field SqlInt4]
> runSelect conn (proc () -> optionalRestrict (valuesSafe l) -< (.> 100000)) :: IO [Maybe Int]
[Nothing]

> runSelect conn (proc () -> optionalRestrict (valuesSafe l) -< (.> 15)) :: IO [Maybe Int]
[Just 100,Just 1000]

See the documentation of leftJoin for how to use optionalRestrict to replace leftJoin (and by symmetry, rightJoin).

optional

Arguments

:: Default Unpackspec a a

=> SelectArr i a

Input query

-> SelectArr i (MaybeFields a)

The rows of the input query wrapped in "Just", unless the input query has no rows in which case a single row of "Nothing"

NB Opaleye exports Opaleye.Table.optional from the top level. If you want this optional you will have to import it from this module.

Convenient access to lateral left/right join functionality. Performs a LATERAL LEFT JOIN under the hood and has behaviour equivalent to the following Haskell function:

optional :: [a] -> [Maybe a]
optional q = case q of
    [] -> [Nothing]
    xs -> map Just xs

That is, if q :: SelectArr i a returns no rows, optional q :: SelectArr i (MaybeFields a) returns exactly one "Nothing" row. Otherwise, optional q returns exactly the rows of q wrapped in "Just". For example,

> let l1 = ["one", "two", "three"] :: [Field SqlText]
> runSelect conn (optional (valuesSafe l1)) :: IO [Maybe String]
[Just "one", Just "two", Just "three"]

> let l2 = [] :: [Field SqlText]
> runSelect conn (optional (valuesSafe l2)) :: IO [Maybe String]
[Nothing]

optionalRestrict is a special case of optional and could be written in terms of optional as follows (except that optionalRestrict doesn't use LATERAL under the hood and optional does).

optionalRestrict q = optional $ proc cond -> do
  a <- q -< ()
  restrict -< cond a
  returnA -< a

Direct access to joins (not recommended)

You probably want use the alternatives listed at the top of this module instead of these. The use of the Default NullMaker typeclass means that the compiler will have trouble inferring types. It is strongly recommended that you provide full type signatures when using the join functions. Example specialization:

leftJoin :: Select (Field a, Field b)
         -> Select (Field c, FieldNullable d)
         -> (((Field a, Field b), (Field c, FieldNullable d)) -> Field SqlBool)
         -> Select ((Field a, Field b), (FieldNullable c, FieldNullable d))

leftJoin

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsR nullableFieldsR)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (fieldsL, nullableFieldsR)

Left join

We suggest you use optionalRestrict instead. Instead of writing "leftJoin qL qR cond" you can write

proc () -> do
  fieldsL <- qL -< ()
  maybeFieldsR <- optionalRestrict qR -< curry cond fieldsL
  returnA -< (fieldsL, maybeFieldsR)

Typically everything except the optionalRestrict line can be inlined in surrounding arrow notation. In such cases, readability and maintainibility increase dramatically.

leftJoinA

Arguments

:: (Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsR nullableFieldsR)

=> Select fieldsR

Right query

-> SelectArr (fieldsR -> Field SqlBool) nullableFieldsR

Condition on which to join goes in, left join result comes out

We suggest you don't use this. optionalRestrict is probably better for your use case. leftJoinA is the same as except optionalRestrict without the return type wrapped in MaybeFields.

rightJoin

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsL nullableFieldsL)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (nullableFieldsL, fieldsR)

Right join

We suggest you use optionalRestrict instead. See leftJoin for more details.

fullJoin

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsL nullableFieldsL, Default NullMaker fieldsR nullableFieldsR)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (nullableFieldsL, nullableFieldsR)

Full outer join

Explicit versions

leftJoinExplicit :: Unpackspec fieldsL fieldsL -> Unpackspec fieldsR fieldsR -> NullMaker fieldsR nullableFieldsR -> Select fieldsL -> Select fieldsR -> ((fieldsL, fieldsR) -> Field SqlBool) -> Select (fieldsL, nullableFieldsR)

leftJoinAExplict :: Unpackspec fieldsR fieldsR -> NullMaker fieldsR nullableFieldsR -> Select fieldsR -> SelectArr (fieldsR -> Field SqlBool) nullableFieldsR

rightJoinExplicit :: Unpackspec fieldsL fieldsL -> Unpackspec fieldsR fieldsR -> NullMaker fieldsL nullableFieldsL -> Select fieldsL -> Select fieldsR -> ((fieldsL, fieldsR) -> Field SqlBool) -> Select (nullableFieldsL, fieldsR)

fullJoinExplicit :: Unpackspec fieldsL fieldsL -> Unpackspec fieldsR fieldsR -> NullMaker fieldsL nullableFieldsL -> NullMaker fieldsR nullableFieldsR -> Select fieldsL -> Select fieldsR -> ((fieldsL, fieldsR) -> Field SqlBool) -> Select (nullableFieldsL, nullableFieldsR)

optionalRestrictExplicit :: Unpackspec a a -> Select a -> SelectArr (a -> Field SqlBool) (MaybeFields a)

optionalExplicit :: Unpackspec a a -> SelectArr i a -> SelectArr i (MaybeFields a)

Inferrable versions (deprecated)

leftJoinInferrable

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsR nullableFieldsR, Map Nulled fieldsR ~ nullableFieldsR)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (fieldsL, nullableFieldsR)

Left join

Do not use. Will be deprecated in 0.7. Use optionalRestrict instead.

rightJoinInferrable

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsL nullableFieldsL, Map Nulled fieldsL ~ nullableFieldsL)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (nullableFieldsL, fieldsR)

Right join

Do not use. Will be deprecated in 0.7. Use optionalRestrict instead.

fullJoinInferrable

Arguments

:: (Default Unpackspec fieldsL fieldsL, Default Unpackspec fieldsR fieldsR, Default NullMaker fieldsL nullableFieldsL, Default NullMaker fieldsR nullableFieldsR, Map Nulled fieldsL ~ nullableFieldsL, Map Nulled fieldsR ~ nullableFieldsR)

=> Select fieldsL

Left query

-> Select fieldsR

Right query

-> ((fieldsL, fieldsR) -> Field SqlBool)

Condition on which to join

-> Select (nullableFieldsL, nullableFieldsR)

Full outer join

Do not use. Will be deprecated in 0.7. Use rightJoinF instead.