如果 Slick 3.0.0 中不存在则插入 [英] Insert if not exists in Slick 3.0.0

问题描述

如果不存在，我正在尝试插入，我找到了这篇文章 用于 1.0.1、2.0.

I'm trying to insert if not exists, I found this post for 1.0.1, 2.0.

我在 3.0.0 的文档中发现了使用事务的代码段

val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

val f: Future[Unit] = db.run(a)

如果此结构不存在，我正在努力从插入中编写逻辑.我是 Slick 的新手，对 Scala 的经验很少.这是我尝试插入如果不存在于事务之外...

I'm struggling to write the logic from insert if not exists with this structure. I'm new to Slick and have little experience with Scala. This is my attempt to do insert if not exists outside the transaction...

val result: Future[Boolean] = db.run(products.filter(_.name==="foo").exists.result)
result.map { exists =>  
  if (!exists) {
    products += Product(
      None,
      productName,
      productPrice
    ) 
  }  
}

但是我如何将其放入事务块中?这是我能走的最远的地方:

But how do I put this in the transactionally block? This is the furthest I can go:

val a = (for {
  exists <- products.filter(_.name==="foo").exists.result
  //???  
//    _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

提前致谢

推荐答案

这是我想出的版本:

val a = (
    products.filter(_.name==="foo").exists.result.flatMap { exists => 
      if (!exists) {
        products += Product(
          None,
          productName,
          productPrice
        ) 
      } else {
        DBIO.successful(None) // no-op
      }
    }
).transactionally

虽然有点缺乏，例如返回插入的或现有的对象会很有用.

It's is a bit lacking though, for example it would be useful to return the inserted or existing object.

为了完整起见，这里是表定义:

For completeness, here the table definition:

case class DBProduct(id: Int, uuid: String, name: String, price: BigDecimal)
class Products(tag: Tag) extends Table[DBProduct](tag, "product") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column
  def uuid = column[String]("uuid")
  def name = column[String]("name")
  def price = column[BigDecimal]("price", O.SqlType("decimal(10, 4)"))

  def * = (id, uuid, name, price) <> (DBProduct.tupled, DBProduct.unapply)
}
val products = TableQuery[Products]

我使用的是映射表，该解决方案也适用于元组，只需稍作改动.

I'm using a mapped table, the solution works also for tuples, with minor changes.

另请注意，根据文档，没有必要将 id 定义为可选a> 在插入操作中被忽略:

Note also that it's not necessary to define the id as optional, according to the documentation it's ignored in insert operations:

当您在插入操作中包含 AutoInc 列时，它会被静默忽略，以便数据库可以生成正确的值

When you include an AutoInc column in an insert operation, it is silently ignored, so that the database can generate the proper value

这里是方法:

def insertIfNotExists(productInput: ProductInput): Future[DBProduct] = {

  val productAction = (
    products.filter(_.uuid===productInput.uuid).result.headOption.flatMap { 
    case Some(product) =>
      mylog("product was there: " + product)
      DBIO.successful(product)

    case None =>
      mylog("inserting product")

      val productId =
        (products returning products.map(_.id)) += DBProduct(
            0,
            productInput.uuid,
            productInput.name,
            productInput.price
            )

          val product = productId.map { id => DBProduct(
            id,
            productInput.uuid,
            productInput.name,
            productInput.price
          )
        }
      product
    }
  ).transactionally

  db.run(productAction)
}

(感谢来自 Google 群组主题的 Matthew Pocock 引导我了解此解决方案).

(Thanks Matthew Pocock from Google group thread, for orienting me to this solution).

这篇关于如果 Slick 3.0.0 中不存在则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！