首页 文章

Quill - INSERT INTO SELECT ...语法？

提问于
浏览 次
0

因此，由于异步支持，我最近从ScalikeJDBC转移到了我的scala项目中的Quill .

是否支持任何SQL语法，如下面的示例？

INSERT INTO People (id, cityID)
SELECT 52, Cities.id
FROM Cities
WHERE Cities.name = 'New York City';

INSERT INTO State (id, numCities)
SELECT 4, COUNT(*)
FROM  Cities
WHERE Cities.state = 'NY'

预期的行为

我会尝试类似的东西

quote {
   for {
      count <- query[City].filter(_.state == 'NY').size
   } yield query[State].insert(lift(State(4, count))
}

quote {
   query[City].filter(_.state == 'NY').size.nested.insert(count => lift(State(4, count))
}

但它给出了如下错误：

  • "value map is not a member of Long"关于".size"

  • "nested is not a member of Long"关于".size"

当然，如果我做类似下面的事情，我会收到一堆错误：

quote {
   for {
      count <- List(query[City].filter(_.state == 'NY').size)
   } yield query[State].insert(lift(State(4, count))
}

解决方法

目前唯一的解决方法似乎是运行两个单独的查询（一个用于获取计数，第二个用于插入） . 但是，如果我做了很多基于选择的插入，我认为这会效率低下 .

使用原始查询

我尝试过使用中缀的替代方法，例如：

quote {
    infix"""
      INSERT INTO Languages (id,iso639_1,name)
      VALUES (
        (SELECT x2.id + 1
        FROM (SELECT id FROM Languages UNION SELECT 0) x2
        LEFT JOIN Languages x1 ON (x2.id + 1) = x1.id
        WHERE x1.id IS NULL LIMIT 1),
        'Hello',
        'World'
      );
    """.as[?]
}

但它继续给出这些错误：

[error] (run-main-4a) com.github.mauricio.async.db.mysql.exceptions.MySQLException: Error 1064 - #42000 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Languages (id,iso639_1,name)
[error]       VALUES (
[error]         (SELECT x2.id ' at line 2
[error] com.github.mauricio.async.db.mysql.exceptions.MySQLException: Error 1064 - #42000 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO Languages (id,iso639_1,name)
[error]       VALUES (
[error]         (SELECT x2.id ' at line 2

这是不正确的，因为我复制粘贴原始SQL到一个SQL浏览器，它工作得非常好 .

mysql scala quill

1 回答

  • 0
    final case class TestTable(id: Long, name: String)

  final val ctx = new SqlMirrorContext(MirrorSqlDialect, Literal)

  import ctx._

  final val table  = quote(query[TestTable])
  final val size   = quote(table.filter(_.name == "hello world").size)
  //the insert action
  final val insert = quote {
    (e: TestTable) => table.insert(e)
  }

    //测试

    //INSERT INTO TestTable (id,name) VALUES ((SELECT COUNT(*) FROM TestTable x1 WHERE x1.name = 'hello world'), '')
ctx.run(insert(TestTable(size, "")))
    回复于

相关问题