Carrying out aggregations in Play(Scala) 2.6.x

Repository

https://github.com/playframework/playframework

What Will I Learn?

In this tutorial you will learn the following

• How to carry out aggregations in play(Scala) using slick
• You will learn about Queries, Actions, and Futures

Requirements

The following are required in order to properly follow along this tutorial.

• Intellij IDEA
• sbt
• playframework with slick installed
• Web browser
• Basic knowledge of Scala programming language

Resources

• Slick website:- http://slick.lightbend.com/
• Slick repository:- https://github.com/slick/slick
• Slick License:- BSD-style license

Difficulty

• Intermediate

Tutorial Contents

In today's tutorial we are going to learn how to carry out aggregations in play(scala) 2.6.x . This tutorial is a continuation of previous tutorials. Links to the previous are available on the curriculum section. Before we continue let's look at some basic concepts

Queries, Actions and Futures

i. Queries

Queries are used to build SQL, queries can be modified using map and filter. The filter acts as a Where clause, while the map acts like a select statement.

ii. Actions

Actions are things you can run against a database, and they are used to build a sequence of SQL queries. Actions usually make calls to methods like map and filterwhich chain queries together and transform their results once they have retrieved from the database.

iii Futures

These are the results of actions which support a set of Combinators like map and filter. It used used to transform an asynchronous result of running an action. A query is converted to an action by using the result method.

Aggregations in play

Aggregations are carried out using max, min, sum and avg. When included along with Combinators they can be used to find the maximum, minimum, sum and average of a set of numbers. After running our query we will attempt to carry out aggregations on the results. So let's open our IDE IntelliJ and type the following code into our UserController class found in our app/models package. We are simply adding the code to the already existing code in our model

  def aggMax (): Future[Option[Int]]  = db.run {
    people.map(_.age).max.result
  }

  def aggMin (): Future[Option[Int]] = db.run {
    people.map(_.age).min.result
  }
 
  def aggSum (): Future[Option[Int]] = db.run{
    people.map(_.age).sum.result
  }

  def aggavg (): Future[Option[Int]] = db.run{
    people.map(_.age).avg.result
  }

Code explanation

1. We have defined a function called aggMax (), which takes no parameters, this is because we are not carrying any filtering.

2. Since the result we are going to return from the database is an integer, we will set the Future of the query to an integer by typing Future[Option[Int]]

3. We can the run our query using the db.run() function.

4. We create our query by using the map Combinator. We are selecting the age column, and we want to return the maximum of all ages, so we can achieve that by adding max after the map function.

5. We convert our query into an action by calling the result function

6. We do the same thing for the aggMin (), aggSum (), aggavg (), these functions return our minimum, sum and average respectively. The only difference is that we append min, sum and avg to the others.

These functions will be defined in our models since we are creating our queries.

The next thing we need to do is move to our app/controllers package where we will create our business logic. It's in our controller that we can call all the queries we just defined. We will define different methods for all the functions created in our models, so let's do that.

def aggPeople () = Action.async {
    implicit request =>
      repo.aggMax().map { people =>
        Ok(Json.toJson(people))
      }
  }

  def aggPeopleMin () = Action.async {
    implicit request =>
      repo.aggMin().map { people =>
        Ok(Json.toJson(people))

      }
  }

  def aggPeopleSum() = Action.async {
    implicit request =>
      repo.aggSum().map { people =>
        Ok(Json.toJson(people))

      }
  }

  def aggPeopleAvg() = Action.async {
    implicit request =>
      repo.aggavg().map { people =>
        Ok(Json.toJson(people))
      }
  }

Code explanation

1. We define a method called aggMax () which will calculate the maximum of all the data in the age column. It doesn't accept any parameter and it returns an integer.

2. We uses an asynchronous action which creates an implicit request to calculate the maximum of data and display the data

3. We call the aggMax() function which we created in our models package by making reference to the repo object which references our model known as UserRepository

4. The retrieved data is mapped into a variable known peoplewhich will be displayed as JSON

5. Using the Ok method we display the data as JSON, the toJSON() accepts the data to display as a parameter.

6. The same thing is done for the other methods, aggPeopleMin (), aggPeopleSum(), and aggPeopleAvg(), these methods calculate the minimum, sum and average respectively.

Now to run these queries and view their results we will initiate GET requests inside our routes (conf/routes) file.

GET    /agperson                                  controllers.UserController.aggPeople
GET    /agpersonmin                          controllers.UserController.aggPeopleMin
GET    /aggpersonsum                       controllers.UserController.aggPeopleSum
GET    /aggpersonavg                        controllers.UserController.aggPeopleAvg

Code explanation

1. We create 4 get requests which corresponds to the various controller methods

2. /agperson corresponds to UserController.aggPeople() function which returns the maximum of the ages in the age column.

3. /agpersonmin corresponds to UserController.aggPeopleMin() function which will return the minimum in the age column.

4. /aggpersonsum uses a GET method to calculate the sum of the age in the age column.

5. Typing /aggpersonavg on the browser, would return the average of the age in the age column.

Running the application

To run your application

1. Open command prompt and navigate to your project folder
2. Type sbt
3. Type run
4. To run the individual routes of application type the following routes in your browser differently
   localhost:9000/agperson
localhost:9000/agpersonmin
localhost:9000/aggpersonsum
localhost:9000/aggpersonavg

Screenshot showing result of sum

Proof of Work Done

All the code used in this tutorial can be found below
https://github.com/leczy642/play-scala-slick-CRUD

Curriculum

• Creating a user registration system in play 2.6.x (scala) using mysql
• Creating a user registration system in play 2.6.x (Scala) using mysql part2: Retrieving data in JSON format and navigating with routes
• Retrieving a particular user and initiating GET requests in play 2.6.x(Scala)
• Updating a particular user in play 2.6.x using slick
• Deleting a User and sorting elements in play(Scala) 2.6.x