Mapping a Many-to-Many Releationship Using Doobie

I have two tables in Postgres. The first contains generic information about movies, whereas the latter contains actors.

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
);

Between the two, I defined a many-to-many relationship:

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");

In Scala, I defined the following domain type, representing movies:

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

How can I use the Doobie library to map a join between the three tables above in an instance of the Movie class?

Solution

Doobie is "just" a wrapper around JDBC which provides security against SQL injections. So, how would you query raw SQL to get the data you want? Maybe with something like this (just an example, I haven't checked it):

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")

and this is exactly how I would approach fetching it in 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)

Alternatively, you could use 3 queries:

(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)

This is more verbose (and probably more memory hungry) as it performs the logic of JOIN ON and GROUP BY in your code, but it shows that you can combine several queries into one transaction.