Slick 3.0 - Update Columns in a Table and Return Whole Table Object

Slick 3.0 - Update columns in a table and return whole table object?

I was able to make this work by extending the awesome work by Tim Harper in https://stackoverflow.com/a/28148606/310275

Here's what I've done now:

package utils

import scala.language.existentials

import slick.ast._
import slick.driver.PostgresDriver._
import slick.driver.PostgresDriver.api._
import slick.jdbc.{GetResult, JdbcResultConverterDomain, SetParameter, StaticQuery ⇒ Q, StaticQueryInvoker, StreamingInvokerAction}
import slick.profile.SqlStreamingAction
import slick.relational.{CompiledMapping, ResultConverter}
import slick.util.SQLBuilder

object Slick {
  object UpdateReturning {
    implicit class UpdateReturningInvoker[E, U, C[_]](updateQuery: Query[E, U, C]) {

      def updateReturning[A, F](returningQuery: Query[A, F, C], v: U)(implicit db: Database) = {
        val ResultSetMapping(_,
          CompiledStatement(_, sres: SQLBuilder.Result, _),
          CompiledMapping(_updateConverter, _)) = updateCompiler.run(updateQuery.toNode).tree

        val returningNode = returningQuery.toNode
        val fieldNames = returningNode match {
          case Bind(_, _, Pure(Select(_, col), _)) =>
            List(col.name)
          case Bind(_, _, Pure(ProductNode(children), _)) =>
            children.map { case Select(_, col) => col.name }.toList
          case Bind(_, TableExpansion(_, _, TypeMapping(ProductNode(children), _, _)), Pure(Ref(_), _)) =>
            children.map { case Select(_, col) => col.name }.toList
        }

        implicit val pconv: SetParameter[U] = {
          val ResultSetMapping(_, compiled, CompiledMapping(_converter, _)) = updateCompiler.run(updateQuery.toNode).tree
          val converter = _converter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, U]]
          SetParameter[U] { (value, params) =>
            converter.set(value, params.ps)
          }
        }

        implicit val rconv: GetResult[F] = {
          val ResultSetMapping(_, compiled, CompiledMapping(_converter, _)) = queryCompiler.run(returningNode).tree
          val converter = _converter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, F]]
          GetResult[F] { p => converter.read(p.rs) }
        }

        val fieldsExp = fieldNames.map(quoteIdentifier).mkString(", ")
        val pconvUnit = pconv.applied(v)
        val sql = sres.sql + s" RETURNING ${fieldsExp}"
        val unboundQuery = Q.query[U, F](sql)
        val boundQuery = unboundQuery(v)

        new StreamingInvokerAction[Vector[F], F, Effect] {
          def statements = List(boundQuery.getStatement)
          protected[this] def createInvoker(statements: Iterable[String]) = new StaticQueryInvoker[Unit, F](statements.head, pconvUnit, (), rconv)
          protected[this] def createBuilder = Vector.newBuilder[F]
        }.asInstanceOf[SqlStreamingAction[Vector[F], F, Effect]]
      }
    }
  }

}

It takes his code then transforms the StaticQuery into a SqlStreamingAction

import utils.Slick.UpdateReturning._

val update = Users.filter(_.id === 1).map(_.firstName).
updateReturning(Users.map(_.firstName), ("Jane"))

val firstName = db.run(update.headOption).futureValue.get
firstName must === ("Jane")

Update Scala Slick rows with optional columns

As @insan-e suggested, you could fetch existingUser by userUpdate.id and update using existingUser.field.getOrElse(existingUser.field).

Right now it is not possible to do it in single update query using slick api, this is a known issue

Slick 3.0 Update Row Based on a Condition

Just use the following update:

val updateOperation: DBIO[Int] = all
    .filter(_.registrationHash === hash)
    .map(u => (u.field1, u.field2, u.field3))
    .update((newValue1, newValue2, newValue3))

Notice that updateResult above is DBIO containing Int - being the number of updated rows.

So you can do:

db.run(updateOperation).map { updatedRows =>
     if(updatedRows >= 0) {
         // at least one row was affected, return something here
     } else {
         // nothing got updated, return something here
     }
}

In case you wanted to updated whole row you could use your case class but I doubt that is what you want:

val userRegistration: UserRegistrationCaseClass = ...

val updateOperation: DBIO[Int] = all
    .filter(_.registrationHash === hash)
    .update(newUserRegistration)

Updating db row scala slick

Slick 2.X

You can update a row in two ways (as far as I know), the first one would be to create a row object of type luczekInfo#TableElementTypeand use it to update the full row:

def updateById(id: Long, row: luczekInfo#TableElementType)(implicit s: Session): Boolean =
  luczekInfo.filter(_.id === id).update(row)

Or you can update single fields using:

def updateNameById(mId: Long, mName: String)(implicit s: Session) = {
  val q = for { l <- luczekInfo if l.id === mId } yield l.name
  q.update(mName).run
}

Where I supposed your table has a file called name.

You can find it also on the Slick documentation in the section on updating.

Slick 3.1.X

there's an additional support for the insertOrUpdate (upsert) operation:

luczekInfo.insertOrUpdate(row)

How to return full row using Slick's insertOrUpdate

Solved - posting it incase it helps anyone else trying to do something similar:

//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)  }

---

Related Topics