Slick+Play框架下带HAVING子句的Group By实现问询

Got it, let's walk through how to translate your target SQL query into Slick (integrated with Play Framework) — I'll break it down step by step so you can see how each part maps to Slick's API.

First, let's start with the basics: you'll need a Slick table mapping for your grades table. Here's how that might look (adjust the database driver if you're not using PostgreSQL):

import slick.jdbc.PostgresProfile.api._
import play.api.db.slick.DatabaseConfigProvider
import javax.inject.Inject
import scala.concurrent.Future

// Case class to represent a row in the grades table
case class Grade(student: String, grade: String)

// Slick table mapping
class GradesTable(tag: Tag) extends Table[Grade](tag, "grades") {
  def student = column[String]("student")
  def grade = column[String]("grade")
  
  // Map table columns to the case class
  def * = (student, grade) <> (Grade.tupled, Grade.unapply)
}

// TableQuery instance to query the grades table
val grades = TableQuery[GradesTable]

Translating the HAVING Logic to Slick #

Your original SQL uses two aggregate conditions in the HAVING clause:

1. At least 2 "Excellent" grades
2. Zero "Fail" grades

In Slick, after grouping by student, you define your aggregations and then filter the grouped results (this replaces the SQL HAVING clause). There are two clean ways to implement this:

Option 1: Filter + Length (Simpler Syntax)

This approach uses filter on the grouped records to count matching grades, which is equivalent to your SUM(CASE...) logic:

class GradeService @Inject()(dbConfigProvider: DatabaseConfigProvider) {
  private val dbConfig = dbConfigProvider.get[slick.jdbc.JdbcProfile]
  import dbConfig.profile.api._

  def getEligibleStudents(): Future[Seq[String]] = {
    val eligibleQuery = grades
      .groupBy(_.student) // Group records by student name
      .map { case (student, group) =>
        // Calculate counts for each condition
        val excellentCount = group.map(_.grade).filter(_ === "Excellent").length
        val failCount = group.map(_.grade).filter(_ === "Fail").length
        
        // Return student + counts for filtering
        (student, excellentCount, failCount)
      }
      // Apply the HAVING-like filter
      .filter { case (_, excellentCount, failCount) =>
        excellentCount >= 2 && failCount === 0
      }
      // Extract just the student names
      .map(_._1)

    // Execute the query and return the result
    dbConfig.db.run(eligibleQuery.result)
  }
}

Option 2: Explicit CASE Expressions (Matches Original SQL)

If you prefer syntax that mirrors your original SQL more closely, you can use Slick's Case class to replicate the SUM(CASE...) logic:

import slick.lifted.Case

class GradeService @Inject()(dbConfigProvider: DatabaseConfigProvider) {
  private val dbConfig = dbConfigProvider.get[slick.jdbc.JdbcProfile]
  import dbConfig.profile.api._

  def getEligibleStudents(): Future[Seq[String]] = {
    val eligibleQuery = grades
      .groupBy(_.student)
      .map { case (student, group) =>
        // Count "Excellent" grades using CASE
        val excellentCount = group.map(g =>
          Case.If(g.grade === "Excellent").Then(1).Else(0)
        ).sum.getOrElse(0)
        
        // Count "Fail" grades using CASE
        val failCount = group.map(g =>
          Case.If(g.grade === "Fail").Then(1).Else(0)
        ).sum.getOrElse(0)
        
        (student, excellentCount, failCount)
      }
      .filter { case (_, ec, fc) => ec >= 2 && fc === 0 }
      .map(_._1)

    dbConfig.db.run(eligibleQuery.result)
  }
}

Key Notes #

• Slick doesn't have a direct having method — instead, you define your aggregations in the map after groupBy, then use filter to apply the HAVING conditions.
• Both approaches will generate SQL that's nearly identical to your original query.
• In Play, you'll typically inject DatabaseConfigProvider to get access to the database connection and run the query as a Future.

内容的提问来源于stack exchange，提问作者cmhteixeira