如何使用Doobie映射Postgres中电影与演员表的多对多关联关系

实现方案 #

你可以通过以下两种常用方式实现映射，优先推荐第一种Postgres原生聚合方案，性能更高仅需一次查询： #

方案1：使用Postgres array_agg 聚合函数一次性查询

前置准备

首先确保你已经引入了Doobie Postgres扩展依赖（sbt配置示例）：

libraryDependencies += "org.tpolecat" %% "doobie-postgres" % "你的Doobie版本号"

代码实现

import doobie._
import doobie.implicits._
// 导入Postgres专属类型映射，支持数组转Scala List
import doobie.postgres.implicits._
// 导入自动派生工具，自动生成case class的映射规则
import doobie.generic.auto._
import java.util.UUID

// 你的领域类
case class Movie(id: String, title: String, year: Int, actors: List[String], director: String)

def listAllMovies: ConnectionIO[List[Movie]] =
  sql"""
    SELECT 
      m."ID", 
      m."TITLE", 
      m."YEAR", 
      -- 处理无演员的场景，返回空数组而非null
      COALESCE(array_agg(a."NAME") FILTER (WHERE a."ID" IS NOT NULL), '{}') AS actors, 
      m."DIRECTOR"
    FROM "MOVIES" m
    LEFT JOIN "MOVIES_ACTORS" ma ON m."ID" = ma."ID_MOVIES"
    LEFT JOIN "ACTORS" a ON ma."ID_ACTORS" = a."ID"
    GROUP BY m."ID", m."TITLE", m."YEAR", m."DIRECTOR"
  """.query[Movie].to[List]

如果担心自动派生的字段顺序匹配问题，可以显式定义映射规则，稳定性更高：

implicit val movieRead: Read[Movie] =
  Read[(UUID, String, Int, List[String], String)].map {
    case (id, title, year, actors, director) =>
      Movie(id.toString, title, year, actors, director)
  }

方案2：跨数据库兼容的两次查询组装

如果需要兼容不支持数组聚合的数据库，可以分两次查询再组装：

def listAllMoviesCrossDB: ConnectionIO[List[Movie]] = for {
  // 第一步查所有电影基础信息
  movies <- sql"""SELECT "ID", "TITLE", "YEAR", "DIRECTOR" FROM "MOVIES"""".query[(UUID, String, Int, String)].to[List]
  movieIds = movies.map(_._1)
  // 第二步批量查所有电影关联的演员
  actorMap <- if (movieIds.isEmpty) {
    ConnectionIO.pure(Map.empty[UUID, List[String]])
  } else {
    sql"""
      SELECT ma."ID_MOVIES", a."NAME"
      FROM "MOVIES_ACTORS" ma
      JOIN "ACTORS" a ON ma."ID_ACTORS" = a."ID"
      WHERE ma."ID_MOVIES" = ANY($movieIds)
    """.query[(UUID, String)].to[List].map { list =>
      list.groupMap(_._1)(_._2)
    }
  }
} yield movies.map { case (id, title, year, director) =>
  Movie(id.toString, title, year, actorMap.getOrElse(id, Nil), director)
}

注意事项

1. 建议将Movie类的id字段类型改为UUID而非String，类型更安全，不需要额外做类型转换，Doobie原生支持Postgres UUID类型映射。
2. 方案1的SQL中使用LEFT JOIN而非INNER JOIN，避免遗漏没有关联演员的电影。

内容的提问来源于stack exchange，提问作者riccardo.cardin