CODEWITHSUNDEEP
sqlscalaslick
Mon Calamari
Mon Calamari

Reputation: 4463

Mapping Slick SQL Query results to case classes

I have a complex sql query made of several joins/left joins which result type looks as follows:

val query = sql"""
     // body of query
   """.as[(Long, String, String, Int, Long, Long, String, Int, Option[Int], Option[Int], Option[String], Option[Timestamp], Option[Timestamp])]

The result of running this query is a list of tuples with 10+ properties. The list is processed to produce case classes:

 db.run(query).map(_.groupBy { case (p1, p2, p3, p4, _, _, _, _, _, _, _, _, _) =>
  (p1, p2, p3, p4)
 }.map { case ((o1, co2, o3, o4), o5) =>
  CaseClass1(o1, co2, o3, Seq(o4), o5.groupBy {
    case (_, _, _, _, u1, _, _, _, _, _, _, _, _) => u1
  }.map { case (t1, t2) =>
    CaseClass2(t1, t2.flatMap {
      case (_, _, _, _, _, _, _, _, Some(n1), Some(n2), Some(n3), Some(n4), Some(n5)) => Some(CaseClass3(n1, n2, n3, n4, n5))
      case _ => None
    }, t2.map {
      case (_, _, _, _, _, m1, m2, m3, _, _, _, _, _) => CaseClass4(m1, m2, m3, l1)
    })
  }.toSeq)
}

The process of mapping result set to case classes itself is not very extensible and gives me headache every time I add new property to be selected from the query. Is there a simpler way to map the result set to case classes?

Upvotes: 3

Views: 3532

Answers (1)

jazmit
jazmit

Reputation: 5410

Yes, there certainly is.

First create a case class to replace your tuple, let's call it MyMatch.

case class MyMatch( p1: Long, p2: String ...)

Then, create a GetResult and retrieve your result with this class

implicit val getMyMatch = GetResult(r => MyMatch(r.<<, r.<<, r.<<, ...)


val query = sql"""
     // body of query
   """.as[MyMatch]

You can then use groupBy with the new case class properties.

db.run(query).map(_.groupBy { x => (x.p1, x.p2, x.p3, x.p4) }

The flatmap can become

flatMap { x => for { n1 <- x.n1; n2 <- x.n2 ...} yield CaseClass3(n1, n2, ...) }

Upvotes: 5

Related Questions