How to return a full string using Slick insertOrUpdate

Question

I am currently studying Play2, Scala, and Slick 3.1, and I am pretty stuck in the syntax for using insertOrUpdate and wondering if anyone can help me.

What I want to do is return the full row when using insertOrUpdate, including the auto inc primary key, but I managed to return the number of updated / inserted rows.

Here is my table definition:

package models

final case class Report(session_id: Option[Long], session_name: String, tester_name: String, date: String, jira_ref: String,
                  duration: String, environment: String, notes: Option[String])

trait ReportDBTableDefinitions {

  import slick.driver.PostgresDriver.api._

  class Reports(tag: Tag) extends Table[Report](tag, "REPORTS") {

    def session_id = column[Long]("SESSION_ID", O.PrimaryKey, O.AutoInc)
    def session_name = column[String]("SESSION_NAME")
    def tester_name = column[String]("TESTER_NAME")
    def date = column[String]("DATE")
    def jira_ref = column[String]("JIRA_REF")
    def duration = column[String]("DURATION")
    def environment = column[String]("ENVIRONMENT")
    def notes = column[Option[String]]("NOTES")

    def * = (session_id.?, session_name, tester_name, date, jira_ref, duration, environment, notes) <> (Report.tupled, Report.unapply)
  }

  lazy val reportsTable = TableQuery[Reports]

}

Run codeHide result

Here is the section of my DAO that relates to insertOrUpdate, and it works just fine, but only returns the number of rows updated / inserted:

package models

import com.google.inject.Inject
import play.api.db.slick.DatabaseConfigProvider
import scala.concurrent.Future

class ReportsDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends DAOSlick {

  import driver.api._

  def save_report(report: Report): Future[Int] = {
    dbConfig.db.run(reportsTable.insertOrUpdate(report).transactionally)
  }
}

Run codeHide result

I tried playing with a "return", but I can’t get the syntax I need and continue to get type mismatches, for example. below does not compile (because it is probably completely wrong!)

 def save_report(report: Report): Future[Report] = {
    dbConfig.db.run(reportsTable.returning(reportsTable).insertOrUpdate(report))
  }

Run codeHide result

- Scala Slick, , - .

Answer 1

- , - - :

//will return the new session_id on insert, and None on update
  def save_report(report: Report): Future[Option[Long]] = {
    val insertQuery = (reportsTable returning reportsTable.map(_.session_id)).insertOrUpdate(report)
    dbConfig.db.run(insertQuery)
  }

Hide result

- insertOrUpdate , , , , , , .

Answer 2

, Id (returning(reportsTable.map(_.session_id))),

, , , , .

. .

def getReportDBIO(id: Long): DBIO[Report] = reportsTable.filter(_.session_id === id).result.head

def save_report(report: Report): Future[Report] = {
  val query = reportsTable.filter(_.session_id === report.session_id)
  val existsAction = query.exists.result
  val insertOrUpdateAction = 
  (for { 
     exists <- existsAction
     result <- exists match {
        case true => 
        query.update(report).flatMap {_ => getReportDBIO(report.session_id)}.transactionally
        case false => {
           val insertAction = reportsTable.returning(reportsTable.map(_.session_id)) += report
           val finalAction = insertAction.flatMap( id => getReportDBIO(id)).transactionally //transactionally is important
            finalAction
        }
     }
  } yield result).transactionally

  dbConfig.db.run(insertOrUpdateAction)
}

insertOrUpdate

Answer 3

You can return the full string, but this is an option, as indicated in the documentation, it will be empty during the upgrade and will be (...) representing the inserted row in the insert.

So the correct code will be

    def save_report(report: Report): Future[Option[Report]] = {dbConfig.db.run(reportsTable.returning(reportsTable).insertOrUpdate(report))}