Paging and Dynamic Sorting with Slick and Scala

Get the source code: https://github.com/pcleary00/play-angularjs

Introduction

This last week, I started to get more familiar with Slick.  I have a ton of experience with straight JDBC, as well as ORM frameworks like JPA and EclipseLink.  Slick is an interesting twist on Persistence, kind of like a "FRM", or "Functional Relational Mapper" if you will.

This blog will show some of the tricks I pulled off with Slick to create a function which can dynamically sort and page data using Slick.  I found a few examples on the usual suspects (StackOverflow and Google Groups); however my example was somewhat different.  I thought it would be useful for people looking to do the same, also always interested in any feedback.

This is a somewhat typical problem in any Web Application.  You need to provide a Web API that allows you to page and sort data from your database.  While there are a lot of nice solutions in MongoDB and Cassandra, I still largely deal with relational databases.

The Application

The project that we will be working with is the AngularJS Tutorial. The application is a Play application built using the Play Framework 2.11.

The AngularJS tutorial works on a Phone web site.  Appropriately, our persistence layer will have a Phone object, with a phones table.

To get started, simply setup a new play application.  The full source code is at https://github.com/pcleary00/play-angularjs.

---

Defining the WebAPI

Our sample application will have a Web API that is built to be able to page and sort Phones from our database. There are a lot of ways to setup paging in a WebAPI, but we will choose the following form:
/api/phones?sort=(columnName:sortOrder;columnName2:sortOrder2;...)&pageNumber=1&pageSize=10

---

Creating the Persistence Layer

We will be using Slick to model our table.  This is pretty simple. We simple create a Phones object that is a Slick Table. The table will contain Phone instances, our case class that represents our Phone model. Take note of the fields in the Phone table and case class, they have to align. Also note that the names of the fields in the Phone model correspond to the field names for phones from the AngularJS tutorial.

	case class Phone(id: String, age: Int, imageUrl: String, name: String, snippet: String, carrier: Option[String] = None)
	object Phones extends Table[Phone]("phone") {
	def id = column[String]("id", PrimaryKey)
	def age = column[Int]("age")
	def imageUrl = column[String]("image_url")
	def name = column[String]("name")
	def snippet = column[String]("snippet")
	def carrier = column[Option[String]]("carrier")
	def * = id ~ age ~ imageUrl ~ name ~ snippet ~ carrier <>(Phone, Phone.unapply _)
	lazy val database = Database.forDataSource(DB.getDataSource())
	}

view raw gistfile1.scala hosted with ❤ by GitHub

There should be little of surprise in the example. This is taken directly from the Slick examples.

---

Stop! Let's add a little sorting framework!

This is a pretty simple framework. There is likely a better way to do this. All I did was create a little framework that allows me to pull the sort string that is passed in on our Web API into something more useful.

	/**
	* Little Sort Library for parsing from and to sort expressions
	* A sort expression is a sequence of column - sort order that are typically passed
	* in a URL. An examprle would be:
	* http://localhost:9000/api/phones?sort=(age:desc;name:asc)
	*/
	sealed class SortDirection(val direction: String) {}
	object SortDirection {
	def parse(sort: String): SortDirection = sort match {
	case s if sort.equalsIgnoreCase("desc") => Desc
	case _ => Asc
	}
	}
	case object Asc extends SortDirection("asc")
	case object Desc extends SortDirection("desc")
	object SortBy {
	def parse(sortBy: String): Seq[(String, SortDirection)] = {
	sortBy.stripPrefix("(").stripSuffix(")").split(';').map(
	semiColonDelimited => semiColonDelimited.trim()).map(
	trimmed => trimmed.split(':')).map(
	sortColOrdArr => (sortColOrdArr(0), SortDirection.parse(sortColOrdArr(1)))
	)
	}
	}

view raw gistfile1.scala hosted with ❤ by GitHub

I would love for someone to look at that SortBy.parse function, oi! With this sort framework in place, I can easily take a query parameter passed in on the Web API (through the Controller) and generate a Sequence of Sort directives that I will consume in my Slick code. The following code shows how we parse our Web API method and call the Sort framework. This code lives in the Controller for our little app.

	def page = Action { implicit request =>
	val pageNumber = request.queryString.get("pageNumber").getOrElse(Seq("1"))(0).toInt
	val pageSize = request.queryString.get("pageSize").getOrElse(Seq("10"))(0).toInt
	val sort = request.queryString.get("sort") match {
	case Some(s) => SortBy.parse(s(0))
	case _ => Seq(("name", Desc))
	}
	println("\r\n\r\n!!!Paging " + pageNumber + "; " + pageSize + "; " + sort)
	val results = Phones.page(pageNumber, pageSize, sort)
	Ok(Json.toJson(results))
	}

view raw gistfile1.scala hosted with ❤ by GitHub

---

Hey! How did you generate JSON?

I won't go into detail here, perhaps a future post. But, I built my implicit JSON Reads and Writes, as well as a way to generate JSON from a Tuple, which represents a Page

	implicit val phoneReads = Json.reads[Phone]
	implicit val phoneWrites = Json.writes[Phone]
	implicit val pageWrites = (
	(__ \ 'rows).write[Seq[Phone]] and
	(__ \ 'count).write[Int]
	).tupled : Writes[(Seq[Phone], Int)]

view raw gistfile1.scala hosted with ❤ by GitHub

---

Stop dilly dallying and page already!

	*/
	def page(pageNumber: Int, pageSize: Int, sort: Seq[(String, SortDirection)]): (Seq[Phone], Int) = {
	database.withSession {
	val offset = (pageNumber - 1) * pageSize
	// Here we specify the query as a var, because we are going to change it multiple times
	// start with a simple query over the phones
	var query = Query(Phones)
	// This is building the sort clause, matching on each column in the sort
	sort.foreach {
	sortTuple =>
	val (sortColumn, sortDirection) = sortTuple
	query = query.sortBy(sortColumn match {
	case "id" => if (sortDirection == Desc) _.id.desc else _.id.asc
	case "age" => if (sortDirection == Desc) _.age.desc else _.age.asc
	case "snippet" => if (sortDirection == Desc) _.snippet.desc else _.snippet.asc
	case _ => if (sortDirection == Desc) _.name.desc else _.name.asc
	})
	}
	// The "list" method actually executes the query
	val phones = query.drop(offset).take(pageSize).list
	// This finds the total count of all phones in the system
	val totalPhoneCount = Query(query.length).first
	(phones, totalPhoneCount)
	}
	}

view raw gistfile1.scala hosted with ❤ by GitHub

Here is a breakdown of what is going on here in the page function(that lives inside the Phones object):

• page function parameters - Pretty self explanatory, pageNumber is the page requested, pageSize is the number of Phones to return on the page. The sort is a little tricky. This is the result of our little Sort parser, which is a Sequence of column names and sort orders (SortDirection).
• page function return - The function returns a Tuple. The first element in the tuple is a Sequence of Phone instances. The second element in the tuple is the total number of Phones that exist in the system.
• calculate the offset - This calculates the offset for the start of our page
• apply the dynamic sort - We will iterate over the Sequence that is provided that contains all the sorts that we need to apply. We add a sort expression for each column that is specified, and then we consider the sort direction (Ascending or Descending) to apply to the sort column.
• run the query - The statement that drop.take applies the paging. It indicates to drop the first x results, and from that point, take the number of results specified. It is important to note that we do not actually execute the query until the list command is run.
• get the total count matching the query - this one was a little tricky for me to figure out. There has been a little change in how to do this over the past 9 months, so some examples I found were dated. This method works with Slick 1.0.1. All we do is execute the same query that we created, except we create a new Query running query.length It looks really strange, but all query.length does is essentially a COUNT(*) over the same query. To be honest, it would be nice if the slick folk made the count an action; so instead of Query(query.length).first we could simply run query.length

That all there is to my little example, the most relevant parts to this post are there at the end.