In a Yesod application using the Esqueleto EDSL, I desire to perform an anti-join with a table living in Haskell.
In pure PostgreSQL, the query would look like this:
CREATE TEMPORARY TABLE antijoin_demo (x INT);
INSERT INTO antijoin_demo (x)
VALUES (2),(4),(6),(8),(10),(12),(14),(16),(18)
;
SELECT *
FROM
antijoin_demo
FULL OUTER JOIN
(VALUES (3),(6),(9),(12),(15),(18)) AS t(y)
ON x = y
WHERE x IS NULL OR y IS NULL
ORDER BY x,y
;
The result being: 2,3,4,8,9,10,14,15,16
, which are all numbers that only occur in one of the two tables, but not the numbers 6,12,18
which are contained in both tables.
The problem is that the List [3,6,9,12,15,18]
is not contained in my DB, but is only a Haskell list. Unfortunately, esqueleto does not seem to have VALUES
as a table expression?
The function Database.Esqueleto.valList
only allows me to compute one side of the full anti-join like so:
-- Model:
MyTable
x Int
-- Code:
do
let xs = [2,4,6,8,10,12,14,16,18]
ys = [3,6,9,12,15,18] -- not a constant, but computed in Haskell, outside the DB
runDB $ mapM_ (insert_ . MyTable) xs
-- Esqueleto Query:
zs <- runDB $ select $ do
xs <- from $ table @MyTable
where_ $ (xs ^. MyTableX `E.notIn` E.valList ys)
return xs
So this returns all numbers from the DB table MyTable
that are not contained in the Haskell list ys
, i.e. [2,4,8,10,14,16]
.
How do I compute the other half of the anti-join with esqueleto, i.e. the numbers from the Haskell list ys
which are not inside the DB MyTable
, i.e. [3,9,15]
?
Of course, a workaround would be to read the entire Table MyTable
into Haskell Memory and compute the anti-join with Data.Set
operations, but this somewhat defeats the purpose of the DB.
Another workaround bould be to write ys
into another table, but I am not sure how to use temporary tables with esqueleto either.
I expect both MyTable
and ys
to be large (hundred-thousands) and the resulting differences to be small (less than a dozen).
Thanks to @JosephSible, the solution was using the values
function, which is only contained in the PostgreSQL module.
For completeness, here is the anti join code:
-- Code:
do
let xs = [2,4,6,8,10,12,14,16,18]
ys = [3,6,9,12,15,18] -- not a constant, but computed in Haskell, outside the DB
ys' = fromList (val <$> ys) -- don´t forget to apply val to elements
runDB $ mapM_ (insert_ . MyTable) xs
-- Esqueleto Query:
zs <- runDB $ select $ do
(x :& y) <- from $
table @MyTable
`fullOuterJoin`
values ys'
`on` (\(x :& y) -> x ?. MyTableX ==. y)
where_ $ (isNothing (x ?. MyTableX) ||. isNothing y)
return (x ?. MyTableX, y)
where zs :: [(Value (Maybe Int), Value (Maybe Int))]
It would be nice, if zs
could somehow be returned with Either
instead of a Pair of Maybes, but this is a minor detail.