Mapping SQL Joins using Anorm

Anorm is a Scala framework that is a fairly thin veneer over JDBC that allows you to write SQL queries and map results into Scala objects. The examples easily found on the web have a tendency to be fairly simple. One of the first problems I ran into was mapping a Parent-Child hierarchy where the parent has a collection of values from a different table.

For this post, I’m using a simple, contrived schema that looks like the following:

CREATE TABLE USER (
  id         SERIAL,
  user_name  VARCHAR(100),
  CONSTRAINT pk_user PRIMARY KEY (id)
);
 
CREATE TABLE email (
  user_id    LONG,
  email      VARCHAR(100),
  CONSTRAINT pk_email PRIMARY_KEY (user_id, email)
);
ALTER TABLE email ADD CONSTRAINT fk_email_user FOREIGN_KEY (user_id) REFERENCES USER (id);
 
CREATE TABLE phone (
  user_id    LONG,
  phone      VARCHAR(11),
CONSTRAINT pk_phone PRIMARY_KEY (user_id, phone)
);
ALTER TABLE phone ADD CONSTRAINT fk_phone_user FOREIGN_KEY (user_id) REFERENCES USER (id);

The Simple Case

In the simplest case, Anorm allows you to map the results of a query to a Scala case class like the following:

case class User(id:Long, name: String)
object User {
  def rowMapper = {
      long("id") ~
      str("user_name") map {
        case id ~ name => User(id, name)
      }
  }
}
 
def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      SQL("SELECT id, user_name FROM user WHERE user.id = {id}")
        .on("id" -> id)
        .as(User.rowMapper singleOpt)
  }
}

The query is executed and the results of the query are mapped to the User using a RowMapper which converts the result columns into Scala types and ultimately to a Scala User object that you’ve defined.

Joins

But what if you want a more complex object, such as adding Phone numbers and Email addresses to your user object? Lets say you want something more like the following:

case class User(id:Long, name: String, emails: List[String], phones: List[String])
object User {
  def rowMapper = {
      long("id") ~
      str("user_name") ~
      (str("email") ?) ~
      (str("number") ?) map {
        case id ~ name ~ email ~ number => ((id, name), email, number)
      }
  }
}

This row mapper doesn’t return a User object directly, but rather the columns grouped into a Triple (with id and name as the first part of the Triple).

Anorm doesn’t have a lot of support for that out of the box, but Scala’s built in functions for dealing with Lists and Maps have the tools that you need. Take a look at the following example. If you’re new to Scala, good luck wrapping your brain around it.

def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      SQL(
        """SELECT user_name, email.email, phone.number
          |FROM user
          |LEFT JOIN email ON email.user_id = user.id
          |LEFT JOIN phone ON phone.user_id = user.id
          |WHERE user.id = {id}""".stripMargin)
        .on("id" -> id)
        .as(User.rowMapper *)
        .groupBy(_._1)
        .map {
          case ((dbId, name), rest) => User(dbId, name, rest.unzip3._2.map(_.orNull), rest.unzip3._3.map(_.orNull))
        }.headOption
  }
}

But we can break down those steps a little bit, include the type declarations of what happens at each step to make it more clear as to what’s being done. Using those type declarations you end up with something like the following.

def getUser(id: String): Option[User] = {
  DB.withConnection {
    implicit conn =>
      val result: List[((Long, String), Option[String], Option[String])] = SQL(
        """SELECT user_name, email.email, phone.number
          |FROM user
          |LEFT JOIN email ON email.user_id = user.id
          |LEFT JOIN phone ON phone.user_id = user.id
          |WHERE user.id = {id}""".stripMargin)
        .on("id" -> id)
        .as(User.rowMapper *)
 
      val queryGroupedByUser: Map[(Long, String), List[((Long, String), Option[String], Option[String])]]
        = result.groupBy(_._1)
 
      val listOfUser: Iterable[User] = queryGroupedByUser.map {
        case ((dbId, name), rest) => {
          val emails: List[String] = rest.unzip3._2.map(_.orNull) // convert Option[String]s to List[String] where Some[String]
          val phones: List[String] = rest.unzip3._3.map(_.orNull) // convert Option[String]s to List[String] where Some[String]
          User(dbId, name, emails, phones)
        }
      }
 
      listOfUser.headOption
  }
}

Let’s break that down a little more:

val result: List[((Long, String), Option[String], Option[String])] = SQL(
  """SELECT user_name, email.email, phone.number
    |FROM user
    |LEFT JOIN email ON email.user_id = user.id
    |LEFT JOIN phone ON phone.user_id = user.id
    |WHERE user.id = {id}""".stripMargin)
  .on("id" -> id)
  .as(User.rowMapper *)

This code creates a List as you can see from the type declaration. The List contains an entry for each row returned in the result set. Because we used JOIN clauses, we might have gotten back many rows. For example, if a user had 2 emails the results might have looked like:

id, name, email, number
1, Geoff, geoff@example.com, 15135551212
1, Geoff, geoff2@example.com, 15135551212

The resulting Scala List that directly contains the data from that result set. But we take an extra step of grouping the basic User data (the parent) into its own Tuple which we’ll use later to identify the unique Users. The Scala list of the above result set would contain:

List(((1, "Geoff"), Some("geoff@example.com"), Some("15135551212")), 
     ((1, "Geoff"), Some("geoff2@example.com"), Some("15135551212")))

Next we create a map of the results where the key to the map is the unique users:

val queryGroupedByUser: Map[(Long, String), List[((Long, String), Option[String], Option[String])]]
  = result.groupBy(_._1)

From that list, we create a map, where the keys of the map are the unique parent objects. This turns the list shown above into a map like:

Map((1, "Geoff"), 
  List(((1, "Geoff"), Some("geoff@example.com"), Some("15135551212")), 
       ((1, "Geoff"), Some("geoff2@example.com"), Some("15135551212")))
)

This mapping will work if there are many keys returned as well (assuming you were querying by something non-unique). In that case your map will contain one entry for each of the unique parents.

Finally, we need to take apart the Map and turn it into our domain object:

val listOfUser: Iterable[User] = queryGroupedByUser.map {
  case ((dbId, name), rest) => {
    val emails: List[String] = rest.unzip3._2.map(_.orNull) // convert Option[String]s to List[String] where not null
    val phones: List[String] = rest.unzip3._3.map(_.orNull) // convert Option[String]s to List[String] where not null
    User(dbId, name, emails, phones)
  }
}

The case statement destructures the Map back into the key containing the basic user information and then the list of all the other data associated with that user. rest.unzip3 turns the List(A, B, C) into (List[A], List[B], List[C]). _.2 takes the second element out of the Triple, in this case the List[String] containing the emails. We then map over them to get the value or null from the Option[String] to create a list of the items that are not null. The same process is done for emails and phones. Those values along with the key from the map are used to create the instances of our Users. In this case, since we only expect one based on an id, we also use listOfUser.headOption to get the first element of the list (or None if the list is empty).

Hopefully breaking down the Scala into smaller chunks will help some people understand how this stuff works.

Scala and Adding New Syntax

One interesting thing about some languages is their support for adding new syntax. While all languages have the ability to add new functions or types some have specific properties that make it easy to add what looks like new built-in syntax.

Scala is an Object Oriented language. You can declare classes and objects, do inheritance and composition, and all the other things you might expect from an OO language. Scala is also a Functional language because functions are first-class citizens (also called a functor). And when I say Scala is an OO language I really mean it: everything is an Object. Even functions are Objects. (Chew on that one for a bit.)

Scala also supports the idea of optional parenthesis for method calls that only take a single argument (Note: This applies to method calls on object only. Not to functions.). This ends up being for a very practical reason. Take the following example:

1 + 2

This is a very nice way to write an addition operation. In reality what’s happening is:

1.+(2)

1 is an object and + is a method on that object that takes a single parameter. Applying the previous rule we get to remove the dot and the the parenthesis. Which allows us to write our previous example 1 + 2.

The good news is they bring this consistency to the language as a whole, so any method call can optionally use the dot. Any call to a method that only takes a single parameter can exclude the parenthesis around its arguments. These features make it pretty easy to emulate the built-in syntax of a language.

Your Own While Loop

Let’s say I want to write my own while loop:

def mywhile(condition: => Boolean)(command: => Unit) {
    if (condition) {
        command
        mywhile(condition)(command)
    }    
}
 
var x = 1
mywhile(x < 100000) {
    println(x)
    x += 1 
}

As you can see, I end up calling mywhile the same as I would call a built-in while. This is implemented as a tail-recursive function. If the condition is met, the command is executed. The function then recurses, calling itself to continue. x < 100000 is an anonymous function that returns a boolean expression.

Your Own Do…While Loop

A while loop can be built using just a single function. What if you want to create a do…while loop instead? In this case you can make use of the OO/functional hybrid.

class Repeater(command: => Unit){
    final def aslongas(condition: => Boolean)  {
        command
        if (condition) aslongas(condition)
    }
}
 
def mydo(command: => Unit): Repeater = {
    new Repeater(command)
}
 
var x = 0
mydo { 
    x += 1
    println(x) 
} aslongas (x < 100000)

In this case I use recursion again to do the looping. But I use an Object to bind the command to and an aslongas method to run that command and check the looping condition. I use a function mydo to bootstrap an instance of the Repeater class. Scala gives us the ability to use functions and objects when they make sense.

Why Should You Care?

Ok, so you’re not going to write your own while loops. The language has them built-in already. But what this allows you to see is how you can add new “syntax”. That ability makes it quite convenient and easy to write higher-order syntax to solve application specific problems or to create DSLs.

Update: Changed the until name to ‘aslongas’ since it really wasn’t until the condition was met.