Slick: DAO optional filtering

// 1. Let's create a new table with Play Evolutions (https://pastebin.com/5qhdsZp2)
// 5.sql:
# students schema, building table

# --- !Ups
CREATE TABLE `building` (
    `building_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `number` TINYINT(3) UNSIGNED NULL COMMENT 'building number, might be NULL for upcoming buildings',
    `name` VARCHAR(128) NOT NULL COMMENT 'building name',
    PRIMARY KEY (`building_id`)
) COMMENT 'table to store University Campus buildings' COLLATE 'utf8_general_ci' ENGINE InnoDB;

INSERT INTO `building` (`number`, `name`) VALUES
  (1, 'Main campus'),
  (2, 'Math campus'),
  (3, 'Shujaa campus'),
  (NULL, 'Scotland Yard');

# --- !Downs
DROP TABLE IF EXISTS `building`;



'// BuildingDao.scala:
package dao

import javax.inject.Inject
import scala.concurrent.Future

import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.jdbc.JdbcProfile
import slick.lifted.ProvenShape

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

  import profile.api._

  case class Building(buildingId: Option[Long], number: Option[Int], name: String)

  private class BuildingTable(tag: Tag) extends Table[Building](tag, "building") {
    def buildingId: Rep[Long] = column[Long]("building_id", O.PrimaryKey, O.AutoInc)
    def number: Rep[Option[Int]] = column[Option[Int]]("number")
    def name: Rep[String] = column[String]("name")

    override def * : ProvenShape[Building] = (buildingId.?, number, name) <> (Building.tupled, Building.unapply)
  }

  private lazy val table = TableQuery[BuildingTable]

  def findByNumber(number: Int): Future[Option[Building]] = {
    val query = table.filter(_.number === Option(number)).result
    println(query.statements.mkString)
    db.run(query.headOption)
  }
}



// TestController.scala:
package controllers

import javax.inject.Inject

import scala.concurrent.ExecutionContext

import dao.BuildingDao
import play.api.mvc._

class TestController @Inject()(
    buildingDao: BuildingDao,
    cc: ControllerComponents
  )(implicit ec: ExecutionContext) extends AbstractController(cc) {

  // add to routes: GET    /building/:buildingNumber    controllers.TestController.getBuilding(buildingNumber: Long)
  def getBuilding(number: Int): Action[AnyContent] = Action.async {
    buildingDao.findByNumber(number) map {
      case Some(building) => Ok(s"Result: $building")
      case None => NotFound(s"Building $number not found")
    }
  }
}



// Let's test via browser:
// http://localhost:9000/building/1
// select `building_id`, `number`, `name` from `building` where `number` = 1
// Result: Building(Some(1),Some(1),Main campus)

// http://localhost:9000/building/2
// select `building_id`, `number`, `name` from `building` where `number` = 2
// Result: Building(Some(2),Some(2),Math campus)

// http://localhost:9000/building/3
// select `building_id`, `number`, `name` from `building` where `number` = 3
// Result: Building(Some(3),Some(3),Shujaa campus)

// http://localhost:9000/building/4
// select `building_id`, `number`, `name` from `building` where `number` = 4
// Building 4 not found



// 2. Now let's check Some instead of Option:
val query = table.filter(_.number === Some(number)).result

// Output:
// type mismatch;
//  found   : Some[Int]
//  required: slick.lifted.Rep[?]



// 3. And now let's remove it at all!
val query = table.filter(_.number === number).result

// Output:
// http://localhost:9000/building/1
// select `building_id`, `number`, `name` from `building` where `number` = 1
// Result: Building(Some(1),Some(1),Main campus)

// http://localhost:9000/building/2
// select `building_id`, `number`, `name` from `building` where `number` = 2
// Result: Building(Some(2),Some(2),Math campus)

// http://localhost:9000/building/3
// select `building_id`, `number`, `name` from `building` where `number` = 3
// Result: Building(Some(3),Some(3),Shujaa campus)

// http://localhost:9000/building/4
// select `building_id`, `number`, `name` from `building` where `number` = 4
// Building 4 not found



// As you can see, you can omit Option(number)