scalaslick

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


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?


Solution

  • 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.