How to query an optional column value with a fallback in Slick 3.x

Question

I want to write a query in Slick that fetches a column if it is not null, or defaults to the value of another column if it is null. How can I do that without repeating calls to db.run?

Answer 1

Assuming your table definition looks something like this:

import slick.driver.PostgresDriver.api._ // Import your driver here

class EmployeesTable(tag: Tag) extends Table[(Option[Int], Int)](tag, "employees") {
  def firstCol = column[Option[Int]]("first_col") // This column is nullable
  def secondCol = column[Int]("second_col") // This column is non-nullable

  def * = (firstCol, secondCol)
}

Then your query might look like this:

val query = TableQuery[EmployeesTable].map(employee => employee.firstCol.ifNull(employee.secondCol))
val result: Future[Seq[Int]] = db.run(query.result)

This way every null value in first_col will be replaced by a value from second_col. This will be an equivalent of the following SQL query:

select coalesce("first_col", "second_col") from "employees"