Slick: countDistinct

// Testing Slick countDistinct

// Prerequisites:
// Setup Play Evolutions (https://pastebin.com/5qhdsZp2) and create "students" table (https://pastebin.com/GK2A0eVx)

// 2.sql:
# students schema, student table

# --- !Ups

CREATE TABLE `student` (
    `student_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `name` VARCHAR(64) NOT NULL COMMENT 'student name',
    `birthday` DATE NOT NULL COMMENT 'day of birthday',
    `sex` ENUM('Male','Female') NOT NULL COMMENT 'sex: male/female',
    `admission_year` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'year of admission',
    `faculty_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'reference to faculty table',
    PRIMARY KEY (`student_id`),
    INDEX `IDX_student_admission_year` (`admission_year`),
    INDEX `FK_student_faculty` (`faculty_id`),
    CONSTRAINT `FK_student_faculty` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`faculty_id`) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT 'student table' COLLATE 'utf8_general_ci' ENGINE InnoDB;

# --- !Downs

DROP TABLE IF EXISTS `student`;

'
// insert some data:
INSERT INTO `students`.`faculty` (`name`, `isArts`) VALUES ('Mathematics', 0);
INSERT INTO `students`.`student` (`name`, `birthday`, `sex`, `admission_year`, `faculty_id`) VALUES ('Tommy', '2010-08-19', 'Male', 2010, 1);
INSERT INTO `students`.`student` (`name`, `birthday`, `sex`, `admission_year`, `faculty_id`) VALUES ('Bobby', '2015-08-19', 'Male', 2011, 1);
INSERT INTO `students`.`student` (`name`, `birthday`, `sex`, `admission_year`, `faculty_id`) VALUES ('Jessica', '2011-08-19', 'Female', 2010, 1);

// StudentDAO.scala:
import java.sql.Timestamp
import javax.inject.Inject
import scala.concurrent.Future
import play.api.db.slick._
import play.db.NamedDatabase
import slick.jdbc.JdbcProfile
import slick.lifted.ProvenShape

case class Student(studentId: Option[Long], name: String, birthday: Timestamp, sex: String, year: Int, facultyId: Long) {
  require(Set("Male", "Female") contains sex)
}

class StudentDao @Inject()(
    @NamedDatabase("students") protected val dbConfigProvider: DatabaseConfigProvider
  ) extends HasDatabaseConfigProvider[JdbcProfile] {

  import profile.api._

  private class StudentTable(tag: Tag) extends Table[Student](tag, "student") {
    def studentId: Rep[Long] = column[Long]("student_id", O.PrimaryKey, O.AutoInc)
    def name: Rep[String] = column[String]("name")
    def birthday: Rep[Timestamp] = column[Timestamp]("birthday")
    def sex: Rep[String] = column[String]("sex")
    def year: Rep[Int] = column[Int]("admission_year")
    def facultyId: Rep[Long] = column[Long]("faculty_id")

    override def * : ProvenShape[Student] = (studentId.?, name, birthday, sex, year,
      facultyId) <> (Student.tupled, Student.unapply)
  }

  private lazy val table = TableQuery[StudentTable]

  def getAll: Future[Seq[Student]] = {
    val query = table.result
    println(query.statements.mkString)
    db.run(query)
  }

  def getDistinct: Future[Int] = {
    val query = table.map(student => student.year).distinct.length.result // or just "countDistinct" instead of "distinct.length"
    println(query.statements.mkString)
    db.run(query)
  }
}

// StudentController.scala:
import javax.inject.Inject
import scala.concurrent.ExecutionContext
import scala.util.control.NonFatal
import dao.StudentDao
import play.api.mvc._

/**
 * StudentsController
 * @param studentDao Students Data Access Object
 * @param cc Controller Components
 */
class StudentsController @Inject()(
    studentDao: StudentDao,
    cc: ControllerComponents
  )(implicit val ec: ExecutionContext) extends AbstractController(cc) {

  // add to routes: GET    /test/distinct    controllers.StudentsController.getDistinct
  def getDistinct: Action[AnyContent] = Action.async {
    studentDao.getDistinct map { count =>
      Ok(s"There are $count different years stored in DB")
    } recover {
      case NonFatal(ex) => BadRequest(s"Error occured: $ex")
    }
  }
}

// Come on!
// Call: http://localhost:9000/test/distinct
// Stdout: select count(1) from (select distinct `admission_year` from `student`) x2
// Output: There are 2 different years stored in DB