使用Doobie映射多對多關系

我在 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類的實體中映射上述三個表之間的連接？

uj5u.com熱心網友回復：

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)

這更加冗長（并且可能需要更多記憶體），因為它在您的代碼中執行 JOIN ON 和 GROUP BY 的邏輯，但它表明您可以將多個查詢組合到一個事務中。

標籤：PostgreSQL 斯卡拉 小猫 杜比

上一篇：postgres：臨時列默認值是唯一且不可為空的，不依賴于序列？

下一篇：加權隨機選擇