文章/答案/技术大牛

发布

社区首页 >问答首页 >使用Doobie映射多到多的关系

问使用Doobie映射多到多的关系
EN

Stack Overflow用户

提问于 2021-11-04 20:49:13

回答 1查看 437关注 0票数 1

我在Postgres有两张桌子。第一个包含关于电影的一般信息，而后者包含演员。

CREATE TABLE "MOVIES" (
  "ID" uuid NOT NULL,
  "TITLE" character varying NOT NULL,
  "YEAR" smallint NOT NULL,
  "DIRECTOR" character varying NOT NULL
);

CREATE TABLE "ACTORS" (
  "ID" serial NOT NULL,
  PRIMARY KEY ("ID"),
  "NAME" character varying NOT NULL
);

在这两者之间，我定义了一种多到多的关系：

CREATE TABLE "MOVIES_ACTORS" (
  "ID_MOVIES" uuid NOT NULL,
  "ID_ACTORS" integer NOT NULL
);

ALTER TABLE "MOVIES_ACTORS"
ADD CONSTRAINT "MOVIES_ACTORS_ID_MOVIES_ID_ACTORS" PRIMARY KEY ("ID_MOVIES", "ID_ACTORS");
ALTER TABLE "MOVIES_ACTORS"
ADD FOREIGN KEY ("ID_MOVIES") REFERENCES "MOVIES" ("ID");
ALTER TABLE "MOVIES_ACTORS"
ADD FOREIGN KEY ("ID_ACTORS") REFERENCES "ACTORS" ("ID");

在Scala中，我定义了以下域类型，表示电影：

case class Movie(id: String, title: String, year: Int, actors: List[String], director: String)

如何使用Doobie库在Movie类的实例中映射上述三个表之间的联接？

scala-cats

doobie

postgresql

scala

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-11-05 11:25:27

Doobie是JDBC的“包装器”，它提供了针对SQL注入的安全性。那么，如何查询原始SQL以获得所需的数据？也许是这样的(举个例子，我还没有检查过)：

SELECT m."ID",
       m."TITLE",
       m."YEAR",
       array_agg(a."NAME") as "ACTORS",
       m."DIRECTOR"
FROM "MOVIES" m
JOIN "MOVIES_ACTORS" ma ON m."ID" = ma."ID_MOVIES"
JOIN "ACTORS" a ON ma."ID_ACTORS" = a."ID"
GROUP BY (m."ID",
          m."TITLE",
          m."YEAR",
          m."DIRECTOR")

这正是我在Doobie中获取它的方法：

// import doobie normal utils
// import postgresql extensions for PG arrays and uuids

sql"""
  |SELECT m."ID",
  |       m."TITLE",
  |       m."YEAR",
  |       array_agg(a."NAME") as "ACTORS",
  |       m."DIRECTOR"
  |FROM "MOVIES" m
  |JOIN "MOVIES_ACTORS" ma ON m."ID" = ma."ID_MOVIES"
  |JOIN "ACTORS" a ON ma."ID_ACTORS" = a."ID"
  |GROUP BY (m."ID",
  |          m."TITLE",
  |          m."YEAR",
  |          m."DIRECTOR")
  |""".stripMargin
  .query[Movies] // requires values to be fetched in the same order as in case class
  .to[List]
  .transact(transactor)

或者，您可以使用3个查询：

(for {
  // fetch movies
  movies <- sql"""SELECT m."ID",
                 |       m."TITLE",
                 |       m."YEAR",
                 |       m."DIRECTOR"
                 |FROM movies
                 |""".stripMargin
              .query[UUID, String, String, String]
              .to[List]

  // fetch joins by movies IDs
  pairs <- NonEmptyList.fromList(movies.map(_._1)) match {
    // query if there is something to join
    case Some(ids) =>
     (sql"""SELECT "MOVIES_ID",
           |       "ACTORS_ID"
           |FROM "MOVIES_ACTORS"
           |WHERE""".stripMargin ++
        Fragments.in(fr""" "MOVIES_ID" """, ids))
       .query[(UUID, Int)].to[List]
    // avoid query altogether since condition would be empty
    case None =>
      List.empty[(UUID, Int)].pure[ConnectionIO]
  }

  // fetch actors by IDs
  actors <- NonEmptyList.fromList(pairs.map(_._2)) match {
    // query if there is something to join
    case Some(ids) =>
     (sql"""SELECT "ID",
           |       "NAME"
           |FROM "ACTORS"
           |WHERE""".stripMargin ++
        Fragments.in(fr""" "ID" """, ids))
       .query[(Int, String)].to[List]
    // avoid query altogether since condition would be empty
    case None =>
      List.empty[(Int, String)].pure[ConnectionIO]
  }
} yield {
  // combine 3 results into 1
  movies.map { case (movieId, title, year, director) =>
    val actorIds = pairs.collect {
      // get actorId if first of the pair is == movieId
      case (`movieId`, actorId) => actorId
    }.toSet
    val movieActors = actors.collect {
      // get actor name if id among actors from movie
      case (id, name) if actorsIds.contains(id) => name
    }
    Movie(movieId, title, year, movieActors, director)
  }
})
  .transact(transactor)

在代码中执行联接和分组的逻辑时，这会更冗长(而且可能更需要内存)，但它表明可以将多个查询合并到一个事务中。

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/69845657

复制

相似问题

问使用Doobie映射多到多的关系
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Doobie映射多到多的关系EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Doobie映射多到多的关系
EN