使用连接查询后的Scala快速更新表 [英] Scala slick update table after querying with a join

问题描述

我想更新表，但需要根据某些条件选择行。以下代码编译得很好，但引发运行时异常：

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[SlickException: A query for an UPDATE statement must resolve to a comprehension with a single table -- Unsupported shape: Comprehension s2, Some(Apply Function =), None, ConstArray(), None, None, None, None]]

以下是该函数(其目的是仅允许合格用户进行更新)：

def updateRecord(record: Record)(implicit loggedInUserId: User) = {
    val q = records.withFilter(_.id === record.id).join(companies).on(_.companyId === _.id).filter(_._2.userid === loggedInUserId)
    val recordToUpdate = q.map { case (r, c) => r }
    val action = for {
      c <- recordToUpdate.update(record)
    } yield (c)
    ... // there are other actions in the for comprehension, removed them for clarity

我以为映射的结果是记录表中的一行(不是元组)，但错误似乎表明我没有更新"单个"表。

还是有更好的方式执行查询+更新？

推荐答案

是，您似乎尝试同时更新两个表。

也许您应该尝试一下

  def updateRecord(record: Record)(implicit loggedInUserId: User): Future[Int] = {
    val recordToUpdate = records.filter(_.id === record.id)

    val q = recordToUpdate
      .join(companies).on(_.companyId === _.id)
      .filter(_._2.userid === loggedInUserId)
      .exists

    val action = for {
      c <- recordToUpdate.update(record)
//      ...
    } yield c

    for {
      isLoggedIn <- db.run(q.result)
      if isLoggedIn
      c <- db.run(action)
    } yield c

  }

---

您也可以尝试

  def updateRecord(record: Record)(implicit loggedInUserId: User): 
        DBIOAction[Int, NoStream, Read with Write with Transactional] = {
    val recordToUpdate = records.filter(_.id === record.id)

    val action =
      recordToUpdate
      .join(companies).on(_.companyId === _.id)
      .filter(_._2.userid === loggedInUserId)
      .exists
      .result

    (for {
      isLoggedIn <- action
      if isLoggedIn
      c <- recordToUpdate.update(record)
//    ...
    } yield c).transactionally
  }

---

应该在没有NoSuchElementException: Action.withFilter failed的情况下工作的变体。基于answer。

  def updateRecord(record: Record)(implicit loggedInUserId: User): 
        DBIOAction[Int, NoStream, Read with Write with Transactional] = {
    val recordToUpdate = records.filter(_.id === record.id)

    val action =
      recordToUpdate
      .join(companies).on(_.companyId === _.id)
      .filter(_._2.userid === loggedInUserId)
      .exists
      .result

    action.flatMap {
      case true => for {
        c <- recordToUpdate.update(record)
      //    ...
      } yield c

      case false => DBIO.successful(0) /*DBIO.failed(new IllegalStateException)*/
    }.transactionally
  }

这篇关于使用连接查询后的Scala快速更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！