How to write nested queries in select clause

问题:

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;

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))

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

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?

回答1:

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.

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

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")
}

The result is this:

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