如何在 select 子句中编写嵌套查询 [英] How to write nested queries in select clause

问题描述

我正在尝试使用 SLICK 1.0.0 生成此 SQL:

I'm trying to produce this SQL with SLICK 1.0.0:

    select
    cat.categoryId,
    cat.title,
    (
      select
        count(product.productId)
      from
        products product
        right join products_categories productCategory on productCategory.productId = product.productId
        right join categories c on c.categoryId = productCategory.categoryId
      where
        c.leftValue >= cat.leftValue and
        c.rightValue <= cat.rightValue
    ) as productCount
from
    categories cat
where
    cat.parentCategoryId = 2;

我最成功的尝试是(我去掉了joins"部分，所以它更具可读性):

My most successful attempt is (I dropped the "joins" part, so it's more readable):

def subQuery(c: CategoriesTable.type) = (for {
        p <- ProductsTable

      } yield(p.id.count))
      for {
        c <- CategoriesTable
        if (c.parentId === 2)
      } yield(c.id, c.title, (subQuery(c).asColumn))

在子查询中产生缺少括号的 SQL:

which produces the SQL lacking parenthesis in subquery:

   select 
    x2.categoryId, 
    x2.title, 
    select count(x3.productId) from products x3 
   from 
    categories x2 
   where x2.parentCategoryId = 2

这显然是无效的 SQL任何想法如何让 SLICK 将这些括号放在正确的位置?或者也许有不同的方法来实现这一目标?

which is obviously invalid SQL Any thoughts how to have SLICK put these parenthesis in the right place? Or maybe there is a different way to achieve this?

推荐答案

我从未使用过 Slick 或 ScalaQuery，因此找出如何实现这一点是一次冒险.Slick 是非常可扩展的，但是关于扩展的文档有点棘手.它可能已经存在，但这是我想出的.如果我做错了什么，请纠正我.

I never used Slick or ScalaQuery so it was quite an adventure to find out how to achieve this. Slick is very extensible, but the documentation on extending is a bit tricky. It might already exist, but this is what I came up with. If I have done something incorrect, please correct me.

首先我们需要创建一个自定义驱动程序.我扩展了 H2Driver 以便能够轻松测试.

First we need to create a custom driver. I extended the H2Driver to be able to test easily.

trait CustomDriver extends H2Driver {

  // make sure we create our query builder
  override def createQueryBuilder(input: QueryBuilderInput): QueryBuilder = 
    new QueryBuilder(input)

  // extend the H2 query builder
  class QueryBuilder(input: QueryBuilderInput) extends super.QueryBuilder(input) {

    // we override the expr method in order to support the 'As' function
    override def expr(n: Node, skipParens: Boolean = false) = n match {

      // if we match our function we simply build the appropriate query
      case CustomDriver.As(column, LiteralNode(name: String)) =>
        b"("
        super.expr(column, skipParens)
        b") as ${name}"

      // we don't know how to handle this, so let super hanle it
      case _ => super.expr(n, skipParens)
    }
  }
}

object CustomDriver extends CustomDriver {
  // simply define 'As' as a function symbol
  val As = new FunctionSymbol("As")

  // we override SimpleSql to add an extra implicit
  trait SimpleQL extends super.SimpleQL {

    // This is the part that makes it easy to use on queries. It's an enrichment class.
    implicit class RichQuery[T: TypeMapper](q: Query[Column[T], T]) {

      // here we redirect our as call to the As method we defined in our custom driver
      def as(name: String) = 
        CustomDriver.As.column[T](Node(q.unpackable.value), name)
    }
  }

  // we need to override simple to use our version
  override val simple: SimpleQL = new SimpleQL {}
}

为了使用它，我们需要导入特定的东西:

In order to use it we need to import specific things:

import CustomDriver.simple._
import Database.threadLocalSession

然后，要使用它，您可以执行以下操作(我在示例中使用了官方 Slick 文档中的表格).

Then, to use it you can do the following (I used the tables from the official Slick documentation in my example).

// first create a function to create a count query
def countCoffees(supID: Column[Int]) =
  for {
    c <- Coffees
    if (c.supID === supID)
  } yield (c.length)

// create the query to combine name and count
val coffeesPerSupplier = 
  for {
    s <- Suppliers
  } yield (s.name, countCoffees(s.id) as "test")

// print out the name and count
coffeesPerSupplier foreach { case (name, count) =>
  println(s"$name has $count type(s) of coffee")
}

结果如下:

Acme, Inc. has 2 type(s) of coffee
Superior Coffee has 2 type(s) of coffee
The High Ground has 1 type(s) of coffee

这篇关于如何在 select 子句中编写嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！