How to increment a column in Slick if it exists otherwise insert it with a default value?-CodePudding

This is my sample case class:

package com.test.models.sample

case class UserVisitFO(
                           id: Long = 0L,
                           userId: Long,
                           visits: Long
                         )

And a table like below:

  class UserVisitTable(tag: Tag) extends Table[UserVisitFO](tag, "user_visit") {

    def id: Rep[Long] = column[Long]("id", O.AutoInc, O.PrimaryKey)

    def userId: Rep[Long] = column[Long]("user_id")

    def visits: Rep[Long] = column[Long]("visits")

    def * : ProvenShape[UserVisitFO] = (
      id,
      userId,
      visits,
    ) <> (UserVisitFO.tupled, UserVisitFO.unapply)
  }

  protected val userVisitTable: TableQuery[UserVisitTable] = TableQuery[UserVisitTable]

I need to insert a new record if user does not exist with visit count of 0 and/or update user record and increment visit count by 1. How can I achieve this?

CodePudding user response：

It would be something like:

val userId = ...

userVisitTable
  .filter(_.userId === userId)
  .map(v => (v.id, v.visits))
  .result
  .headOption
  .flatMap {
    // increment existing
    case Some((id, visits)) =>
      userVisitTable
        .filter(_.userId === userId)
        .map(_.visits)
        .update(visits   1)
    // create new
    case None =>
      userVisitTable  = (userId, 1)
  }
  .transactionally

which can also be written with for-comprehension like

val visitOption = (
  for {
    uv <- userVisitTable if uv.userId === userId
  } yield (uv.id, uv.visits)
).headOption

def updateVisit(id: Long, visits: Long) = (
  for {
    uv <- userVisitTable if uv.id === id
  } yield uv.visits
).update(visits   1)

def createVisits(userId: Long) =
  userVisitsTable  = (userId, 1)

(
 for {
    option <- visitOption.headOption
    _ <- option match {
      case Some((id, visits)) => updateVisits(id, visits)
      case None               => createVisits(userId)
    }
  } yield ()
).transactionally

Then, since you'd get DBIOAction, you would have to run the result into Future with db.run. All these operations are described in the documentation.