How to encode VALUES constant table expression in esqueleto?-CodePudding

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).

CodePudding user response：

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.