Retrieving Data From 2 Tables in play 2.6.x(Scala) Using Slick: Part 1

Repository

https://github.com/playframework/playframework

What Will I Learn?

In this tutorial you will learn the following

• How to carry out joins operations
• The difference between Applicative and Monadic Joins

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

Choose one of the following options:

• Advanced

Tutorial Contents

In today's tutorial we are going to learn how to carry out JOIN operations in play(scala) 2.6.x . This tutorial is a continuation of previous tutorials. You can find previous links in the curriculum section. Before we continue let's look at some basic concepts

Joins, Monadic Joins and Applicative Joins

1. Joins

Join is used to retrieve data from 2 or more tables, based on the related column between them, in other words joins are used to retrieve data from 2 tables using a single query. In slick there are 2 basic Join styles, Applicative and Monadic.

Image illustrating an inner join (Source: Designed by me with adobe fireworks)

2. Monadic joins

Monadic joins are created using the flatMap. Is not possible to carry out Monadic joins in SQL, in slick they can be carried out by compiling them to Applicative joins but this is useful in many cases but not in all cases.

3. Applicative joins

Applicative joins are called are carried out by calling a query that combines two queries into a single query. In Applicative joins the right hand side may not depend on the left hand side.

Carrying out joins in play(Scala)

In carrying our joins we will need to create 2 tables one table called person and the other called address. The person table will contain information about a person like name and age, while the address table will contain fields like address and city which and will hold the address of a particular user. We will create a foreign key in the address table that will link both tables.

To begin we will create 2 case classes, Person and Address and also define a JSON serializer for bot. We will do that by typing the following code

package models

import play.api.libs.json._

case class Person(id: Long, name: String, age: Int)
case class Address(id: Long, personId: Long, address: String, city: String)

object Person {  
  implicit val personFormat = Json.format[Person]
}

object Address {
  implicit val personFormat = Json.format[Address]
}

Code explanation

1. We create a case class called Person which contains 3 parameters id, name and age. This would correspond to our first table definition in our models package.

2. We create another case class called Address which takes 4 parameters id, personId, address, and city

3. A Person Object is created which contains the JSON serializer, the JSON serializer is called using the json.format() function which holds the Person array as a parameter.

4. A second Object is created for the Address case class known as Address. It functions like the first object, but contains the JSON serializer for the Address.

Now it's time to define our tables, since we are retrieving data from 2 tables, we will have to define both tables and create a TableQuery Object.

So let's create a file in our app/models package, you can name yours as you desire, but I choose to call mine PersonRepository and we will define our first table known as PeopleTable and give it 3 columns, id, name and age

private class PeopleTable(tag: Tag) extends Table[Person](tag, "people") {

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    def name = column[String]("name")

    def age = column[Int]("age")

    def * = (id, name, age) <> ((Person.apply _).tupled, Person.unapply)
  }

private val people = TableQuery[PeopleTable]

Code Explanation

1. We create a private class known as PeopleTable that extends the Person Table. The tag means that our table name will be called people.

2. The id column is created as type Long and it will be defined as Primary key and will auto increment. We do this by setting it to O.PrimaryKey and O.AutoInc.

3. The name and age column are defined as String and Integer respectively. The column[String] and column[Int] sets the column to String and Integer.

4. def * defines the table's default projection and sets the id, name and age parameters Person.apply and Person.unapply case class methods respectively.

5. We create a TableQuery object known as people by typing the following code :

private val people = TableQuery[PeopleTable]

Next we define our second table which we will call address, it contains the address and city of a person.

private class AddressTable(tag: Tag) extends Table[Address](tag, "address") {

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    def personId = column[Long]("personId")

    def address = column[String]("address")

    def city = column[String]("city")

    def user = foreignKey("user_fk", personId, people)(_.id)

    def * = (id, personId, address, city) <> ((Address.apply _).tupled, Address.unapply)
  }

  private val addresses =  TableQuery[AddressTable]

Code Explanation

1. Like above we define our address table which extends the Table class the value of the tag means our table will be called address.

2. Our id column is set as primary key and will auto increment when a new row is added to the table.

3. The user column is set as our foreign key, since we need a way to link both tables.

4. def * defines the table's default projection and sets the id, personId, address and city parameters Address.apply and Address.unapply case class methods respectively.

5. We create a TableQuery object known as people by typing the following code :

private val addresses =  TableQuery[AddressTable]

Still inside our PersonRepository file we will type the code to insert sample data and initiate the join query

 def insertAddress (personId: Int, address: String, city: String): Future[Int] = db.run {
    addresses.map(p => (p.personId, p.address, p.city )) +=(personId, address, city)
  }

  def insertPerson (name: String, age: Int): Future[Int]= db.run {
    people.map(p => (p.name,p.age)) +=(name, age)
  }

def innerJoin() : Future[Seq[(Person,Address)]] = db.run{
    people.
      join(addresses).on(_.id === _.personId).result
  }
  

Code Explanation

1. We define a function called insertAddressit takes 3 parameters personId, address and city

2. The db.run function maps our query to a variable, and the values are inserted when we type this +=(personId, address, city)

3. We define another called insertPerson, and it takes 2 parameters name and age, we do the same thing to this function as step 2.

4. we def a function known as innerJoin, which will execute the query for our join operation.

5. We join the people and the address table where the id in people is equal to the userId in address. We execute the query when we add the result function.

Now we move on to our controllers, where we will execute all our queries, controllers can be found inside the app/controllers package. Let's type the following code in our controllers.

 def execInsertAddress(personId: Int, address: String, city: String) = Action.async { implicit request =>
    repo.insertAddress(personId,address,city).map{
      _ =>
        Redirect(routes.PersonController.index())
    }
  }

  def execInsertPerson(name: String, age: Int)  = Action.async {implicit  request =>
    repo.insertPerson(name,age).map{
      _ =>
        Redirect(routes.PersonController.index())
    }
  }

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

Code Explanation

1. We define a method known as execInsertAddress, this method takes 4 parameters personId, address, and city, similar to the insertAddress function created in our models package.

2. An implicit request is initiated to insert values into the address table, and redirect the user back to the index page.

3. the same thing is done for execInsertPerson, which inserts a person's name and age into the people table.

4. To join both tables we define a function known as execInnerJoin and create an implicit request to join both tables using the innerJoin function.

5. We display the data serially as JSON using the Json.toJson(people) function. This cannot be done unless the JSON serialization object is first created.

Next, we need to create a means to navigate our application and to do that we will create GETrequests in our routes file.

GET     /insertaddress/:personid/:address/:city                 controllers.PersonController.execInsertAddress(personId: Int, address: String, city: String)
GET     /insertperson/:name/:age                              controllers.PersonController.execInsertPerson(name: String, age: Int)
GET     /viewinnerjoin                                        controllers.PersonController.execInnerJoin

Code Explanation

1. The first GETstatement maps to PersonController.execInsertAddress()function. The :personid/:address/:city are value that must be entered in your browser if new user will be entered into people table.

2. The second GET statement maps to PersonController.execInsertPerson, which inserts a new user address into the address table. The personid, address and city must be entered into the browser for a new column to be inserted into the table.

3. The third GET statement maps to PersonController.execInnerJoin. This URL when called displays the result of the join.

4. For instance if we want to insert a new address into the address table, where the personid = 1, address = 123 wuse 2, city = Abuja. The URL would look something like this localhost:9000/1/123 wuse 2/Abuja

Finally we need to update our 1.sql file found in conf/evolutions.default with the following code

# --- !Ups

create table "people" (
  "id" bigint generated by default as identity(start with 1) not null primary key,
  "name" varchar not null,
  "age" int not null
);


create table "address" (
"id" bigint generated by default as identity(start with 1) not null primary key,
"personId" int not null,
"address" varchar not null,
"city" varchar not null
);

# --- !Downs

drop table "address" if exists;

drop table "people" if exists;

The above script will run when you run the application for the first time.

Code Explanation

1. The SQL statement is divided into 2 sections # --- !Ups and # --- !Downs. The # --- !Ups is executed if there are no errors, while the # --- !Downs is executed if there are errors.

2. In the # --- !Ups we are creating 2 tables peopleand address, with the SQL statement create table.

3. The first table has 3 columns id, name, and age. The id column has been set as the primary key and will auto increment, the name and age columns have been set to varchar (VarableCharacter, which is an SQL string type) and int (Integer, which is an SQL Integer type) respectively.

4. The second table has 4 columns id, personId, address, city, which are int and varchar respectively. The id column has a type called bigint and has been set as primary key and will auto increment when a new row is added to the database.

5. The # --- !Downs section drops the people and address table if there are any errors.

To run our application type the following address on your browser, localhost:9000. To enter a particular user enter the following localhost:9000/insertperson/***somename***/***someage***. To enter a new address enter the following localhost:9000/insertaddress/***somepersonid***/***someaddress***/***somecity***. To view the results of our join enter the following localhost:9000/viewinnerjoin.

When we run our application for the first time we will get the following message

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
• 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
• Carrying out aggregations in Play(Scala) 2.6.x

Proof of Work Done

Proof of work done can be found here
https://github.com/leczy642/play-scala-slick-JOIN