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)
);</p>
<p>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);</p>
<p>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)
}
}
}
<p>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, [email protected], 15135551212 1, Geoff, [email protected], 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("[email protected]"), Some("15135551212")),
((1, "Geoff"), Some("[email protected]"), 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("[email protected]"), Some("15135551212")),
((1, "Geoff"), Some("[email protected]"), 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.