如何在Esqueleto中用VALUES常量表实现与Haskell列表的全反连接？

需求背景 #

需要在Yesod应用中，基于Esqueleto EDSL实现全反连接：取仅出现在数据库表MyTable或仅出现在Haskell计算列表ys中的元素，排除两边都存在的元素。

对应的纯PostgreSQL查询逻辑如下：

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
; 

预期结果为：2,3,4,8,9,10,14,15,16，即排除同时存在的6,12,18。

现有方案的局限 #

当前仅能通过E.valList实现全反连接的一侧：获取MyTable中不在ys的元素，代码示例：

-- Model定义
MyTable
  x Int

-- 初始化数据
do 
  let xs = [2,4,6,8,10,12,14,16,18]
      ys = [3,6,9,12,15,18] -- 动态计算的Haskell列表，未存储在数据库
  runDB $ mapM_ (insert_ . MyTable) xs 

-- 查询MyTable中不在ys的元素
zs <- runDB $ select $ do
  xs <- from $ table @MyTable
  where_ $ (xs ^. MyTableX `E.notIn` E.valList ys)
  return xs

该代码返回[2,4,8,10,14,16]，但无法直接获取ys中不在MyTable的元素[3,9,15]。同时由于MyTable和ys数据量达数十万级，无法将整张表读入内存用Data.Set计算，也不适合写入临时表。

解决方案 #

利用Esqueleto的values函数将Haskell列表转换为数据库可查询的数据源，结合exists子查询实现双向反连接，最后合并结果：

1. 获取ys中不在MyTable的元素 #

ysNotInDB <- runDB $ select $ do
  -- 将Haskell列表转换为数据库可查询的行集合
  yVal <- from $ values (map E.val ys)
  -- 过滤出MyTable中不存在匹配x的y值
  where_ $ not_ $ exists $ do
    xRow <- from $ table @MyTable
    where_ $ xRow ^. MyTableX ==. yVal
  return yVal

这段代码会生成对应的SQL，在数据库层面完成判断，避免加载全表数据到内存。

2. 合并双向结果得到全反连接 #

将之前获取的zs（MyTable不在ys的元素）和ysNotInDB（ys不在MyTable的元素）合并，即可得到全反连接的完整结果：

-- 提取数值（zs是Entity MyTable类型，ysNotInDB是Value Int类型）
let dbSide = map (\(Entity _ mt) -> mt ^. MyTableX) zs
    listSide = map unValue ysNotInDB
    fullAntijoin = dbSide ++ listSide
-- 可选：排序结果
sortedResult = sort fullAntijoin

完整代码示例 #

import qualified Database.Esqueleto as E
import Database.Persist.Sql (runDB)
import Data.List (sort)

-- 假设Model定义已存在：MyTable x Int

getFullAntijoin :: IO [Int]
getFullAntijoin = do
  let xs = [2,4,6,8,10,12,14,16,18]
      ys = [3,6,9,12,15,18]
  -- 初始化测试数据
  runDB $ mapM_ (insert_ . MyTable) xs

  -- 获取MyTable中不在ys的元素
  dbNotInList <- runDB $ select $ do
    xRow <- from $ table @MyTable
    where_ $ xRow ^. MyTableX `E.notIn` E.valList ys
    return xRow

  -- 获取ys中不在MyTable的元素
  listNotInDB <- runDB $ select $ do
    yVal <- from $ values (map E.val ys)
    where_ $ not_ $ exists $ do
      xRow <- from $ table @MyTable
      where_ $ xRow ^. MyTableX ==. yVal
    return yVal

  -- 提取数值并合并排序
  let dbVals = map (\(Entity _ mt) -> mt ^. MyTableX) dbNotInList
      listVals = map unValue listNotInDB
  return $ sort (dbVals ++ listVals)

这个方案全程在数据库层面完成过滤逻辑，仅将最终的少量结果（十几个）加载到Haskell内存，避免了大数据量的内存开销。

内容的提问来源于stack exchange，提问作者STurtle