Scala下使用jOOQ查询含Array[Long]表获取主问题与子问题ID对

核心思路 #

利用jOOQ对数组类型的操作能力结合数据库的unnest函数完成数组打平，两步完成查询：

1. 筛选出subQuestionIds包含目标子问题ID的所有主问题记录
2. 将数组字段拆分为单行单元素的结构，过滤后得到配对结果

代码实现 #

假设你已经通过jOOQ代码生成了QQuestion表对象，传入的子问题ID列表为targetSubIds: List[Long]，初始化好的jOOQ上下文为ctx：

import org.jooq.impl.DSL._
import scala.jdk.CollectionConverters._

val q = QQuestion.QUESTION

val result = ctx.select(
    q.ID.as("mainQuestionId"),
    unnest(q.SUB_QUESTION_IDS).as("subQuestionId")
  )
  .from(q)
  // 筛选subQuestionIds和目标列表有交集的记录，提前过滤无效数据
  .where(arrayOverlap(q.SUB_QUESTION_IDS, targetSubIds.toArray))
  // 过滤出仅属于目标子问题ID的配对
  .and(field(name("subQuestionId"), classOf[Long]).in(targetSubIds.asJava))
  .fetch()
  // 映射为你需要的(Long, Long)元组
  .map(r => (r.get("mainQuestionId", classOf[Long]), r.get("subQuestionId", classOf[Long])))

兼容方案（数据库不支持unnest时使用） #

如果你的数据库不支持unnest函数，可以先查出符合条件的主问题，在内存侧完成数组打平：

val result = ctx.select(q.ID, q.SUB_QUESTION_IDS)
  .from(q)
  .where(arrayOverlap(q.SUB_QUESTION_IDS, targetSubIds.toArray))
  .fetch()
  .flatMap(r => {
    val mainId = r.get(q.ID)
    val matchedSubIds = r.get(q.SUB_QUESTION_IDS).filter(targetSubIds.contains)
    matchedSubIds.map(subId => (mainId, subId))
  })

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