Scala slick - How to take a column value from different tables

Question

I have 2 tables: Main and Update with columns name, id and department. I would like to read the Main table first, and if the department field value is empty, i could like to read it from Update table.

For example, from the below table, I should get the department value for KING from Update table.

How to achieve this using scala slick with out running plain sql queries?

Main

+-------+----+------------+
+ name  | id | department +            
+-------+----+------------+
| KING  | 10 |            |
| BLAKE | 30 | SALES      |
+-------+----+------------+

Update

+-------+----+------------+
+ name  | id | department +  
+-------+----+------------+
| KING  | 10 | SYSTEMS    |
| BLAKE | 30 | SALES      |
+-------+----+------------+

Here is the plain sql query

SELECT 
m.`name`,
m.`id`,
if(m.`department`='', u.`department`) as `department`
FROM `Main` m
FROM `Update` u ON m.id=u.id

I have following code defined so far ...

case class Main(name: Option[String],
                id: Option[int],
                department: Option[String])

case class Update(name: Option[String],
                  id: Option[int],
                  department: Option[String])

lazy val mainQuery = TableQuery[MainTableDefinition]
lazy val updateQuery = TableQuery[UpdateTableDefinition]

class MainTableDefinition(tag: Tag) extends Table[Main](tag, "Main") {
      def name = column[Option[String]]("name")
      def id = column[Option[int]]("id")
      def department = column[Option[String]]("department")

      override def * =
         (name, id, department)<> (Main.tupled, Main.unapply)
}

class UpdateTableDefinition(tag: Tag) extends Table[Update](tag, "Update") {
      def name = column[Option[String]]("name")
      def id = column[Option[int]]("id")
      def department = column[Option[String]]("department")

      override def * =
         (name, id, department)<> (Update.tupled, Update.unapply)
}

val query = for {
  m <- mainQuery 
  u <- updateQuery if m.id === u.id
} yield (m, u)

db.run(query.to[List].result)

You might want to define a custom function coalesce (postgresql.org/docs/9.2/static/functions-conditional.html) and use it val query = for { m <- mainQuery u <- updateQuery if m.id === u.id department = coalesce(m.department, u.department) } yield (m.id, department) — Arseniy Zhizhelev

Paul Dolega Paul Dolega · Accepted Answer · 2016-11-30T14:47:37

First of all your example:

SELECT 
m.`name`,
m.`id`,
if(m.`department`='', u.`department`) as `department`
FROM `Main` m
FROM `Update` u ON m.id=u.id

seems to fetch a combination of values from two tables.

DB level solution

On DB level maybe something like this would work for you:

val query = for {
  m <- mainQuery 
  u <- updateQuery if m.id === u.id
} yield {
  (m.id, m.name, m.department.ifNull(u.department))
}

which I believe is very close your original, desired query (though to be honest I am not entirely sure how ifNull is translated on all databases).

Application level solution

On application level, assuming that you have single case class:

case class NameDepartment(name: Option[String],
                  id: Option[int],
                  department: Option[String])

(which btw you can you for unpacked type for both table definitions as they are identical) it might look like this:

  val fetchOperation = (for {
    m <- mainQuery 
    u <- updateQuery if m.id === u.id
  } yield (m, u)
  ).result.map { results => 
    results.map { case (m, u) =>
       NameDepartment(m.name, m.id, m.department.orElse(u.department))
     }
  }

Scala slick - How to take a column value from different tables

1 Answers