Scala Anorm zero to many the right way

Question

I have a simple database consisting of 2 tables - movie and comment, where comments are related to movies, and then I have following piece of scala anorm code:

case class Comment(commentId: Long, comment: String)
case class Movie(movieId: Long, name: String, movieType: String)

object MovieDao {
  val movieParser: RowParser[Movie] = {
    long("movieId") ~
    str("name") ~
    str("movieType") map {
      case movieId ~ name ~ movieType => Movie(movieId, name, movieType)
    }
  }

  val commentParser: RowParser[Comment] = {
    long("commentId") ~
    str("comment") map {
      case commentId ~ comment => Comment(commentId, comment)
    }
  }

  def getAll(movieType: String) = DB.withConnection {
    implicit connection =>
      SQL(
        """
          |SELECT
          |movie.movieId,
          |movie.name,
          |movie.movieType,
          |comment.commentId,
          |comment.comment
          |FROM movie
          |LEFT JOIN comment USING(movieId)
          |WHERE movieType = {movieType}
        """.stripMargin)
      .on("movieType" -> movieType)
      .as(((movieParser ~ (commentParser ?)) map (flatten)) *)
      .groupBy(_._1) map {(mc: (Movie, List[(Movie, Option[Comment])])) =>
        mc match {
          case (a, b) => (a, b filter { //filter rows with no comments
            case (c, Some(d)) => true
            case _ => false
          } map(_._2))
        }
      } toList
  }
}

My goal is to return List[(Movie, Option[List[Comment]])] from getAll method, so I can iterate over movies and check if there are any comments as simple as possible, e.i. match None or Some on comments List. I'm currently returning List[(Movie, Option[List[Option[Comment]])] and I'm only able to check size of comments List (thanks to using filter method), which I don't consider as the right way to do it in scala.

My second question is about parsing query itself, I think it's just to complicated the way I did it. Is there any simpler and nicer solution to parse 0..N relation using anorm?

Anorm doesn't have a way of parsing one-to-many relations like that. I did a similar thing in this answer. Which is ultimately why I wrote anorm-relational to cut down on some of the code duplication this caused. (Disclaimer: I am the author of that library, and it's starting to show age) — Michael Zajac

wwkudu wwkudu · Accepted Answer · 2015-09-17T05:56:46

Peter, it's possibly more style than anything dramatically different, but with a MovieComments case class, you could write something like:

case class MovieComments(movie: Movie, comments: List[Comment])

  val movieCommentsP =
    movieParser ~ (commentParser ?) map {
      case movie ~ comment =>
        MovieComments(movie,if (comment.isEmpty) List() else List(comment.get))
  }

  val movieSqlSelector = "m.movieId, m.name, m.movieType"
  val commentSqlSelector = "c.commentId, c.comment"

  def getAll(movieType: String) :List[MovieComments]= DB.withConnection {
    implicit connection =>
      (SQL(
        s"""
          |SELECT
          |$movieSqlSelector,
          |$commentSqlSelector
          |FROM movie
          |LEFT JOIN comment USING(movieId)
          |WHERE movieType = {movieType}
        """.stripMargin)
        .on('movieType -> movieType)
        .as(movieCommentsP *)
        .groupBy(_.movie.movieId) map { 
            case (movieId,movieComments) => 
              MovieComments(
                  movieComments.head.movie,
                  movieComments.flatMap(_.comments))
        }
      ).toList
  }

You may really need an Option[List[Comment]], but wouldn't a List[Comment] do? List() is the "no comment" case after all. (P.S. I find the use of sqlSelector variables helps with refactoring.)

Scala Anorm zero to many the right way

1 Answers