I have been trying to figure out a sensible way to represent Haskell sum types in a SQL backend using persistent.
My target Haskell data type is along the lines of
data Widget = FooWidget Int | BarWidget T.Text
data HElement = HElement
{ name :: T.Text
, widget :: Widget
}
I am modeling these using the following Persistent data types:
Element
name T.Text
Foo
elementId ElementId
size Int
Bar
elementId ElementId
colour T.Text
There will only ever be either a Foo or a Bar for each Element, never both.
I want to use a Left Outer Join
to select all my elements and the corresponding Foo OR Bar. My Esqueleto expression is:
select $
from $ \(elem `LeftOuterJoin` foo `LeftOuterJoin` bar) -> do
on (just (elem ^. ElementId) ==. foo ?. FooElementId)
on (just (elem ^. ElementId) ==. bar ?. BarElementId)
return (elem, foo, bar)
However, when I execute the code, I get the error:
user error (Postgresql.withStmt': bad result status FatalError (("PGRES_FATAL_ERROR","ERROR: missing FROM-clause entry for table
If I remove the second join, giving:
select $
from $ \(elem `LeftOuterJoin` foo) -> do
on (just (elem ^. ElementId) ==. foo ?. FooElementId)
return (elem, foo)
The code runs without error. I'm sure it's obvious but I can't see what I am doing wrong.
EDIT: I found what the problem was; from the docs:
Note carefully that the order of the ON clauses is reversed! You're required to write your ons in reverse order because that helps composability (see the documentation of on for more details).
The following code works (the order of the on
expressions reversed):
select $
from $ \(elem `LeftOuterJoin` foo `LeftOuterJoin` bar) -> do
on (just (elem ^. ElementId) ==. bar ?. BarElementId)
on (just (elem ^. ElementId) ==. foo ?. FooElementId)
return (elem,foo,bar)
Thanks,
Michael
Adding as an answer as suggested by Waldheinz
I found what the problem was; from the docs:
Note carefully that the order of the ON clauses is reversed! You're required to write your ons in reverse order because that helps composability (see the documentation of on for more details).
The following code works (the order of the on
expressions reversed):
select $
from $ \(elem `LeftOuterJoin` foo `LeftOuterJoin` bar) -> do
on (just (elem ^. ElementId) ==. bar ?. BarElementId)
on (just (elem ^. ElementId) ==. foo ?. FooElementId)
return (elem,foo,bar)
Regards,
Michael
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With