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

Repository

https://github.com/playframework/playframework

What Will I Learn?

In this tutorial you will learn the following

• You will learn about the basics of cross JOIN
• You will learn about the basics of left JOIN
• You will learn How to execute a left JOIN operation

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
• Gimp website:- https://www.gimp.org
• Gimp repository:- https://github.com/GNOME/gimp
• Gimp License:- GNU license

Difficulty

• Intermediate

Tutorial Contents

Welcome to today's tutorial, in the previous tutorial we looked the difference between Applicative and monadic joins, in this tutorial we will be looking at how to execute how to execute left JOIN operations in slick. This tutorial promises to be as simple as possible, but for a better understanding you are advised to visit my previous tutorial on this subject found below in the curriculum section.

Cross Join

In creating joins, we constrain the tables using the on constraint. Anytime we create a JOIN, whether it's is an inner join, outer join, left join or right join. If we omit the on constraint we end up with what is known as a cross join. Cross join just includes every row from the left table with every row on the right table.

Left Join

When a left join operation is executed all the rows of the left table is returned, even if there are no matching rows on the right table. What this simply means is that all the values of the left table plus matching values on the right table NULL in case of no matching join predicate will be returned.

In SQL the basic syntax for a left join is:

SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column

Syntax Explanation

1. The basic statement for a JOIN is a select statement, so we are selecting a column in table 1 and another column in table 2 which we want to display.

2. We would first select from table 1 which is the table on the left and using the LEFT JOIN keyword join table 2

3. Using the ON clause we will then match common columns in both tables. NULL is returned if there are no matching predicates.

Consider the following tables, one called people and the other called address we will attempt to display the address and name of people using common fields like id in people table and personId in the address table.

People table

address table

Let's write the query to join our tables together in slick, to do that we will open up our editor (in my case IntelliJ Idea) and type the following code in our model.

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

Code Explanation

1. We define a function called leftjoin. In scala Functions are defined using the def keyword.

2. We will the return a future of Person, and another for the address, Since we are carrying out a join we have to return futures from both tables.

3. Using the first table people, which we call the joinLeft function which accepts as a parameter, the second table, address.

4. the on clause now joins matching columns in both tables, which is the id in table 1 and personId in table 2.

5. Finally we execute our query using result function.

Next we move our controllers, where we will carry out actions for our queries, we can do that by typing the following code

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

Code Explanation

1. In our controller we create a function known as execLeftJoin, this is just a short form for execute left join you can name the function as you wish.

2. In controllers we cannot execute functions without carrying out an implicit request, in our code above we are making reference to the PersonRepository class by calling the repo object.

3. We call our leftJoin function declared in our model. We then map it to a variable known as people to allow us display as JSON

4. to display the data as JSON we call the toJson function which accepts our array of people and address combined together as people.

To access our application we need to create routes, so that we can easily navigate different points of the application. So we will create routes by typing the following codes

GET    /viewleftjoin                                          controllers.PersonController.execLeftJoin

Code Explanation

1. we initiate a GET request which is called when we append /viewleftjoin to our address localhost:9000.

2. Our GET request maps to execLeftJoin function in the PersonController class.

3. The routes are placed in order of priority, and we will place it at the bottom of the routes, we will give high priorities to the routes that are higher up.

After we have inserted some sample data into people and address, we can then view the result of the join by typing localhost/9000/viewleftjoin

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
• Retrieving Data From 2 Tables in play 2.6.x(Scala) Using Slick: Part 1

Proof of Work Done

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