以光滑的方式更新表格的前 n 行 [英] Update top n rows of a table in slick

问题描述

我想在使用 slick 3.0

这是所有版本的更新:

private[this] val active = this.filter(a => a.status =!= AccountStatus.DISABLED)
db.run(
    active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
    .map(account => account.usedBy)
    .update("host-a")
)

我尝试使用此版本但它不起作用并引发异常

I tried to use this version but it didn't work and thrown an exception

private[this] val active = this.filter(a => a.status =!= AccountStatus.DISABLED)
db.run(
    active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
    .take(10)
    .map(account => account.usedBy)
    .update(Option(Host.name))
)

例外

Caused by: slick.SlickException: A query for an UPDATE statement must resolve to a comprehension with a single table -- Unsupported shape: Comprehension s2, Some(Apply Function and), None, ConstArray(), None, None, Some(LiteralNode 100 (volatileHint=false)), None
at slick.driver.JdbcStatementBuilderComponent$QueryBuilder.buildUpdate(JdbcStatementBuilderComponent.scala:447)
at slick.driver.JdbcProfile$$anonfun$updateCompiler$1.apply(JdbcProfile.scala:30)
at slick.driver.JdbcProfile$$anonfun$updateCompiler$1.apply(JdbcProfile.scala:30)
at slick.jdbc.JdbcMappingCompilerComponent$JdbcCodeGen.compileServerSideAndMapping(JdbcMappingCompilerComponent.scala:59)

推荐答案

嗯……这个问题的答案在于你正在尝试做一些 Slick 不应该做的事情.

Well... the answer to this lies in that fact that you are trying to do something which Slick is not supposed to do.

一个非常简单的指南 - 如果有疑问，请考虑使用 SQL 然后转换为 Slick

A very simple guideline - When in doubt think in SQL then transform to Slick

想想你将如何在 SQL 中实现这一点，

Just think how will you achieve this in SQL,

如果我转换你的查询"，

If I transform your "query",

// lets say Host.name = "Awesome-Host"

active.filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
  .take(10)
  .map(account => account.usedBy)
  .update(Option(Host.name))

对于SQL，它会是这样的，

to SQL, it will be something like this,

UPDATE
    active
SET
    used_by = 'Awesome-Host'
WHERE
    used_by IS NULL
    OR used_by = 'Awesome-Host'
LIMIT 10

从 SQL 的角度来看这有点荒谬......

Which is kind of absurd from an SQL perspective...

现在...让我们谈谈您将如何实际使用 SQL 来做到这一点，

Now... Lets talk about how will you actually do it with SQL,

UPDATE
    (
        SELECT 
            *
        FROM
            active
        WHERE
            used_by IS NULL
            OR used_by = 'Awesome-Host'
        LIMIT 10
    ) active_selection
SET
    active_selection.used_by = 'Awesome-Host'

而且...这可以使用子查询转换为 Slick

And... this can be translated to Slick using Sub-Queries

val activeSelection = active
  .filter(a => a.usedBy.isEmpty || a.usedBy === Host.name)
  .take(10)

val updateSelection = activeSelection
  .map(a => a.usedBy)
  .update(Option(Host.name))

这篇关于以光滑的方式更新表格的前 n 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！