Carrying out aggregations in Play(Scala) 2.6.x

Screenshot (67).png

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

We have defined a function called aggMax (), which takes no parameters, this is because we are not carrying any filtering.
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]]
We can the run our query using the db.run() function.
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.
We convert our query into an action by calling the result function
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

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.
We uses an asynchronous action which creates an implicit request to calculate the maximum of data and display the data
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
The retrieved data is mapped into a variable known peoplewhich will be displayed as JSON
Using the Ok method we display the data as JSON, the toJSON() accepts the data to display as a parameter.
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

We create 4 get requests which corresponds to the various controller methods
/agperson corresponds to UserController.aggPeople() function which returns the maximum of the ages in the age column.
/agpersonmin corresponds to UserController.aggPeopleMin() function which will return the minimum in the age column.
/aggpersonsum uses a GET method to calculate the sum of the age in the age column.
Typing /aggpersonavg on the browser, would return the average of the age in the age column.

Running the application

To run your application

Open command prompt and navigate to your project folder
Type sbt
Type run
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

Screenshot (86).png

Proof of Work Done

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

Curriculum

Sort:

Trending

[-]

portugalcoin (76) 7 years ago

Thank you for your contribution.
While I liked the content of your contribution, I would still like to extend one advice for your upcoming contributions:

Put more visual examples in the tutorial.

Looking forward to your upcoming tutorials.

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.

Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]

$0.00

leczy (59) 7 years ago

Thanks @portugalcoin for moderating my tutorial

utopian-io (71) 7 years ago

Hey @leczy
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Contributing on Utopian
Learn how to contribute on our website or by watching this tutorial on Youtube.

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!