TableQuery to case class

By : Gandalf
Source: Stackoverflow.com
Question!

I have some for comprehension loop. Object used here were automatically generated from DB with slick.codegen.SourceCodeGenerator:

for {
  boxer <- Boxers.filter { b => b.address === someAddress }
  fullBoxer <- buildFullBoxer(boxer)
} yield {
  fullBoxer
}

The buildFullBoxer function takes a case class BoxersRow as a parameter so the loop does not compile and generates the error:

type mismatch; found : models.Tables.Boxers required: models.Tables.BoxersRow

The generated schema code is:

case class BoxersRow(id: Long, firstName: String, lastName: String, nick: Option[String] = None, boxingTypeId: Int = 0, birthDate: Option[java.sql.Date] = None, address: Option[String] = None, lastUpdated: java.sql.Timestamp)
implicit def GetResultBoxersRow(implicit e0: GR[Long], e1: GR[String], e2: GR[Option[String]], e3: GR[Int], e4: GR[Option[java.sql.Date]], e5: GR[java.sql.Timestamp]): GR[BoxersRow] = GR{
    prs => import prs._
    BoxersRow.tupled((<<[Long], <<[String], <<[String], <<?[String], <<[Int], <<?[java.sql.Date], <<?[String], <<[java.sql.Timestamp]))
}
class Boxers(_tableTag: Tag) extends Table[BoxersRow](_tableTag, "boxers") {
    def * = (id, firstName, lastName, nick, boxingTypeId, birthDate, address, lastUpdated) <> (BoxersRow.tupled, BoxersRow.unapply)
    def ? = (Rep.Some(id), Rep.Some(firstName), Rep.Some(lastName), nick, Rep.Some(boxingTypeId), birthDate, address, Rep.Some(lastUpdated)).shaped.<>({r=>import r._; _1.map(_=> BoxersRow.tupled((_1.get, _2.get, _3.get, _4, _5.get, _6, _7, _8.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    val id: Rep[Long] = column[Long]("id", O.AutoInc, O.PrimaryKey)
    ....
}
lazy val Boxers = new TableQuery(tag => new Boxers(tag))

Of course I would not like to change automatically generated schema objects. The buildFullBoxer function reads additional data from DB and build a common FullBoxer object containing all necessary data.

private def buildFullBoxer(boxersRow: BoxersRow): DBIO[FullBoxer] = {
    val query = for {
      ((((boxer, fight), division), b1), b2) <-
      Boxers.filter(_.id === boxersRow.id)
        .joinLeft(Fights).on((b, f) => (b.id === f.firstBoxerId) || (b.id === f.secondBoxerId))
        .joinLeft(Divisions).on((bf, d) => bf._2.map { _.divisionId === d.id })
        .joinLeft(Boxers).on((bfd, b1) => bfd._1._2.map { _.firstBoxerId === b1.id } )
        .joinLeft(Boxers).on((bfdb1, b2) => bfdb1._1._1._2.map { _.secondBoxerId === b2.id } )
    } yield (boxer, fight, division, b1, b2)

    val action = query.result.map {case sequence => sequence.groupBy(x => x._1) }.
      map { _.map { case (box, tup) => (box, tup.map { case (b, f, d, b1, b2) => f.map { fight => (fight, d.getOrElse(throw NoDivisionException("No such a division: " + fight.divisionId)), b1.getOrElse(throw NoBoxerException("No boxer with id " + fight.firstBoxerId, Seq.empty, None)), b2.getOrElse(throw NoBoxerException("No boxer with id " + fight.secondBoxerId, Seq.empty, None)), Seq.empty) }  } map (_.map(FullFight.tupled)) flatten ) } toSeq }.
      map {_.map(FullBoxer.tupled).head }
    action
}

How could I pass the case class BoxersRow to buildFullBoxer function in this for comprehension loop?

Regards!

By : Gandalf


Answers
Boxers.filter { b => b.address === someAddress } returns just a query, not a result. A query can be composed and extended. Think of writing a strong typed SQL query. In order to get a result set, you need to run the query respectively action against a database.

So first you will need to create DBIOAction:Boxers.filter(b => b.address === someAddress).result. Actions can again be composed, but not extended anymore.

Secondly run that action against a database: db.run(Boxers.filter(b => b.address === someAddress).result), whereas db is the Database object (see Slick docs). db.run finally return s a Future[Seq[BoxerRow]].

You can then run buildFullBoxer directly using map:

val fullBoxers: Future[Seq[WhateverBuildFullBoxerReturns]] = {
  db.run(Boxers.filter(b => b.address === someAddress).result).map { results =>
    results.map(boxer => buildFullBoxer(boxer))
  }
}
By : Roman


I'm adding another answer here, as you are not taking advantage of slicks querying capabilities. The workflow is generally Query -> Action -> Result. As a rule of thumb, stay as low as possible. This means work with Query until no longer possible. Then combine DBIOAction and finally if you really have to, start combining Future (Results).

One of slicks core features is, that you can combine and mix several queries into one. Something that is not possible in plain SQL. Your use case can easily be solved by mixing two Queries. It could look like this (untested code ahead):

object BoxerQuery {
   val findFullBoxers = {
     Boxers
      .joinLeft(Fights).on((b, f) => (b.id === f.firstBoxerId) || (b.id === f.secondBoxerId))
      .joinLeft(Divisions).on((bf, d) => bf._2.map { _.divisionId === d.id })
      .joinLeft(Boxers).on((bfd, b1) => bfd._1._2.map { _.firstBoxerId === b1.id } )
      .joinLeft(Boxers).on((bfdb1, b2) => bfdb1._1._1._2.map { _.secondBoxerId === b2.id } )
      .map { 
         case ((((boxer, fight), division), b1), b2) => (boxer, fight, division, b1, b2)
       }
   }

   def findFullBoxerByAddress(address: Rep[Address]) = findFullBoxers.filter(fbQuery => fbQuery._1.address === address)

   def findFullBoxerByWebaddress(webaddress: Rep[Webaddress] = findFullBoxers.filter(fbQuery => fbQuery._1.webaddress === webaddress)
}

The whole code block above is still on Query level. You can mix and combine queries as you like. A decent IDE is quite helpful here. If the query finally returns what you need, create an action that returns a FullBoxer:

val action: DBIOAction[Seq[FullBoxer]] = Query.findFullBoxerByAddress(address).result.map(_.groupBy(_._1).map {
  case (boxer, grp) => FullBoxer(
    boxer,
    grp.flatMap(_._2).distinct,
    grp.flatMap(_._3).distinct,
    grp.flatMap(_._4).distinct
  )
})

Now that we have everything in place, run action against the database and fetch all FullBoxer objects in a single round trip:

val fullBoxers: Future[Seq[FullBoxer]] = db.run(action)
By : Roman


This video can help you solving your question :)
By: admin