Query composition

For clarity reasons, it's OK to split a single Query into multiple ones.

Start by importing:

import scalaql._

// Docs classes
import scalaql.docs.Hogwarts._

Assume you'd like to calculate some statistics for adult students:

case class AdultStudent(name: String, faculty: String, age: Int, grade: Double)

case class AdultStudentsStats(
  faculty:  String,
  avgAge:   Double,
  avgGrade: Double)

First, you define a Query to keep only adults:

val adultStudents = select[Student]
  .where(_.age >= 18)
  .map(student => 
    AdultStudent(
      name = student.name, 
      faculty = student.faculty, 
      age = student.age, 
      grade = student.grade
    )
  )
// adultStudents: Query[From[Student], AdultStudent] = FROM(Hogwarts::Student) -> WHERE -> MAP(MdocSession::App::AdultStudent)

Then, you define an aggregation Query:

val adultStats = select[AdultStudent]
  .groupBy(_.faculty)
  .aggregate((faculty, adults) => 
    (
      adults.avgBy(_.age.toDouble) &&
      adults.sumBy(_.grade)
    ).map { case (avgAge, avgGrade) => AdultStudentsStats(faculty, avgAge, avgGrade) }
  )
// adultStats: Query[From[AdultStudent], AdultStudentsStats] = FROM(MdocSession::App::AdultStudent) -> GROUP BY(String) -> AGGREGATE(MdocSession::App::AdultStudentsStats)

It's pretty easy to combine those queries using >>> operator:

val resultQuery = adultStudents >>> adultStats
// resultQuery: Query[From[Student], AdultStudentsStats] = FROM(Hogwarts::Student) -> WHERE -> MAP(MdocSession::App::AdultStudent) AND THEN FROM(MdocSession::App::AdultStudent) -> GROUP BY(String) -> AGGREGATE(MdocSession::App::AdultStudentsStats)

Then you could run this query as usual:

resultQuery
  .show(truncate = false)
  .run(from(students))
// +----------+------------------+--------+
// |faculty   |avgAge            |avgGrade|
// +----------+------------------+--------+
// |Slytherin |18.0              |85.1    |
// |Gryffindor|18.333333333333332|250.9   |
// +----------+------------------+--------+
//